Filtering: Group unique IDs, find conflicting attributes?

williampod · April 22, 2013 12:57PM

Hi All, I was wondering whether you could assist and I will try to explain the best I can as there is a fair bit to this.

I have a set of data with a "Unique ID(1)". This "Unique ID(1)" can have multiple Customers assigned to it and all attributes for the Customers under the unique ID(1) should all be aligned.

One of the attributes assigned to the Customer, is another "Unique ID(2)". I would like to filter where we have conflicting "Unique ID(2)" values under "Unique ID(1)".

So Unique ID(2) <> Unique ID(2)
This could be where the ID is populated and not equal or one is null and the other is not.

I would not like "Unique ID(1)" to be present in the report if its count is just one. In addutuib the "Unique ID(2)" is not a mandatory field, so if it is null, again I do not wish to see this.

I am able to present data where conflicting and null, but this does not meet the criteria above.

paola · April 26, 2013 6:26AM

The data set is not entirely fitting the scenario above, but the formulas below should hopefully give you useful ideas how to address this. What you're trying to do is data validation, making sure that data fits the criteria 1:many or 1:1, so you might want to use Data Validation operation in the DataManager. Please see how it works:
http://tc.visokio.com/videos/?name=DataManagerValidateData&title=Validate+data&lang=gb

If you prefer to use DataExplorer, you should try to use combination of formulas/ filtering and also visual tools, that will enable you to spot discrepancies, select them and analyse in detail. Please see how in the demo file the subsets for some views are set to All data/Filtered data/Select data.
Cust Name for each ID1:

SUBSET_UNIQUESLIST([Legal Name], SUBSET([Unique ID1]))

you should tokenise this field - it is likely to have multiple values, separated by comma - this will enable you to search and filter those values

Unique ID2s for each Legal Name:

SUBSET_UNIQUESLIST([Unique ID2], SUBSET([Legal Name]))

Count of ID2 for each Legal Name:

SUBSET_UNIQUECOUNT([Unique ID2], SUBSET([Legal Name]))

Count of ID2 for each ID1:

SUBSET_UNIQUECOUNT([Unique ID2], subset([Unique ID1]))

Welcome!

Categories

Ideas Parade

Tagged