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...
Solved! Go to Solution.
This worked great. Is there any way I we can get the Output Dataset?
you can use output data tool instead of browse tool to write results in whatever file format you want. Hope this answers your queries.
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 |