Matching one string with two matches

Question

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 2Value 1 Value 2Total Value 1 Vale 2Total5011141234$90 5011141234$905011146789$20 5011146789$20    5011151000$40    5011163012$23

Desired MatchValue 1 Value 2Total5011141234$905011146789$20

Current MatchValue 1 Value 250111412345011141234

Thank you in advance for your time and cooperation.

Best,

juan1

markcurry · Accepted Answer

Hi @juan1

You could join the 2 dataset based on Value 1 and 2.   See the attached workflow, hope that helps.

Mark

markcurry · Accepted Answer

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 2Total Value 1 Vale 2Total5011141234$90 5011141234$50   5011141234$40

Value 1 Value 2TotalRight_Total5011141234$90$505011141234$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.