Deal all,
I am a new to alteryx and cannot seem to find a solution to this.
I have set of values that are the same, but they have different values that identify them. Pretty much, I want to do something like an index match, but the index match always returns the first result of the values instead of both.
For example:
Data set 1 | Data set 2 | |||||
Value 1 | Value 2 | Total | Value 1 | Vale 2 | Total | |
501114 | 1234 | $90 | 501114 | 1234 | $90 | |
501114 | 6789 | $20 | 501114 | 6789 | $20 | |
501115 | 1000 | $40 | ||||
501116 | 3012 | $23 |
Desired Match | ||
Value 1 | Value 2 | Total |
501114 | 1234 | $90 |
501114 | 6789 | $20 |
Current Match | |
Value 1 | Value 2 |
501114 | 1234 |
501114 | 1234 |
Thank you in advance for your time and cooperation.
Best,
juan1
Solved! Go to Solution.
Hi @juan1
You could join the 2 dataset based on Value 1 and 2. See the attached workflow, hope that helps.
Mark
Thanks @markcurry
The problem is that I have several data that I want to keep from the other 2 data sets. Would you suggest to work from the Right and Left side and then union it back together?
Best.
Juan1
Hi @juan1 , exactly you would often use the Union tool after the Join tool to add back in the L and R outputs.
One thing to watch out for though, is make sure that you have unique values in your join. If you were to join the two datasets below :
Data set 1 | Data set 2 | |||||
Value 1 | Value 2 | Total | Value 1 | Vale 2 | Total | |
501114 | 1234 | $90 | 501114 | 1234 | $50 | |
| 501114 | 1234 | $40 |
Value 1 | Value 2 | Total | Right_Total |
501114 | 1234 | $90 | $50 |
501114 | 1234 | $90 | $40 |
You will get this output. So now the sum of your Total field is $180. To prevent this from happening, either use the unique tool before your join, or use the Summarize tool, and group by Value 1, Value 2 and Sum total, and then join on the total amounts per Value 1 and 2.