Data Comparison for multiple columns between two tables

Question

Business Requirement - Currently there are app. 60 attributes/columns to compare between two tables. In future there will be more attributes to compare. Major points to cover in data comparison are:-

* For particular attribute, how many records are populating between current and target states. Purpose is to check population gap
* How many records/unique values are matching between both states

It will be highly appreciated if you suggest how we can automate this process and share sample WF.

Deano478 · Answer

@danilang I'm a bit late to the party but I just want say your solution was very helpful just one quick question what would a possible formula look like to compare the values after the Join?

soccertil1108 · Answer

another source of help would be the Basic Data Profile [Data Investigation category]. In the past I have post processed the statistics passed into the workflow to generate consistency reports and analytics. I think that most of your requirement analysis would be contained in the data profile.

danilang · Answer

Hi Vikas

For comparison between 2 data sets with varying columns, a good strategy is to transpose all the data columns in both sets and the join on key columns+[Name].  After the transpose, the [Name] field will contain the column names from the original data sets.  The Left and Right outputs of the Join tool will contain columns that don't match between the two datasets.  Use a formula tool on the J output to compare the values from each dataset.  You can then produce summary stats on the various columns by grouping by key columns+[Name] and getting the count of values that match, total values from input one and input two, etc.

Dan