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

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

WorkflowResults

Data Comparing and Summary - solution.yxmd