Matching one string with two matches

[juan1]

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

[markcurry · Accepted solution]

Hi @juan1 

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

Mark

[juan1]

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

[markcurry · Accepted solution]

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.