Data Comparing and Summary

[subbarao]

Hi, I have 2 datasets and trying to figure out an easy way to compare the data...

Dataset 1 - 

ID	Metric 1	Metric 2
A1	10	10
A2	20	20
A3	30	30
A4	40	40
A5	50	50
A6	60	60

DateSet 2

ID	Measure 1	Measure 2
A1	10	10
A2	21	20
A3	30	33
A4		40
A5	55	50
A6	60

Output Dataset

ID	Metric 1	Measure 1	Test 1	Metric 2	Measure 2	Test 2
A1	10	10	Y	10	10	Y
A2	20	21	N	20	20	Y
A3	30	30	Y	30	33	N/A
A4	40		N/A		45	N
A5	50	55	N	50	50	Y
A6	60	60	Y	60		N/A

I was able to achieve this using Join and creating the Test 1 and Test 2 using Formula. 

I have 100 such Metric/Measure combinations I want to compare. Wanted to check if there is an easier way....

I also want to create a summary of the test results - 

Dataset 3 (Subset of Output above)

ID	Test 1	Test 2
A1	Y	Y
A2	N	Y
A3	Y	N/A
A4	N/A	N
A5	N	Y
A6	Y	N/A

Desired Output - 

	Y	N	N/A
Test 1	3	2	1
Test 2	3	1	2

I was able to achieve this by Selecting each column and doing a union and transposing the data. 

But again, is there an easier way to aggregate 100 columns which does not require the need to do 100 selects...

[ponraj · Accepted solution]

Here is the dynamic workflow you are looking for.  Hope this is helpful. 

WorkflowResults

[subbarao]

This worked great. Is there any way I we can get the Output Dataset?

[ponraj]

you can use output data tool instead of browse tool to write results in whatever file format you want. Hope this answers your queries. 

[subbarao]

Sorry, I was not clear.

My question was - how do I get to "Output Dataset" view posted in my original question (Same view posted below)

Without using Join and formula for each column I am testing...

Output Dataset

ID	Metric 1	Measure 1	Test 1	Metric 2	Measure 2	Test 2
A1	10	10	Y	10	10	Y
A2	20	21	N	20	20	Y
A3	30	30	Y	30	33	N/A
A4	40		N/A		45	N
A5	50	55	N	50	50	Y
A6	60	60	Y	60		N/A

[ponraj]

Here is the dynamic workflow you are looking for.  Hope this is helpful 

WorkflowDataset 3OutputDataset