Join tool duplicate confusion

Hi there

Can someone explain to me in simple term how I can use the join tool to check what does not have a match? The right output seems to generate more duplicates that what was in the Right input file.

How do I just get it to show the exact rows that dont match? The input file has duplicates which I would like to not remove and keep in the output file.

When I try to link the R output to another join, it is generating alot of rows? How do I avoid it?

Thanks!

1.jpg

Join

Accepted answers

All comments

OTrieger

@Alteryxuserhere L and R cannot generate duplicates

OTrieger

Duplicates can be only in J

L - all the items that comes from the Left and does not have matching item
R - all the items that comes from the Right and does not have matching item
J - that is the only output where you might have duplications

Alteryxuserhere

Thanks @OTrieger For the secondary Join tool i used after the R tool, it shows that a larger number of rows are processed. Wouldn't these be duplicates then?

OTrieger

R cannot contain duplicates, as mentioned before, R contains all the items that comes from Right and there is no match for them from the L side.

In your case as you are merging the same source of the data. You might get lots of duplicates in J as you are joining the same source after the first Join. I do not think that you need the 2nd Join, as what you are doing you are taking all the items from the Right side that did not have a match with the Left side and then join them again with the same source of data. in the 2nd Join you will never going to have items in L as you are using the same data source. Why do you have the 2nd Join?

OTrieger

There is a good explanation here with visual explanation
https://help.alteryx.com/current/en/designer/tools/join/join-tool.html#idp336712

Alteryxuserhere

For the first join im using email address as a criteira for the join and i want to find out which rows do not match based on email and then the second join is based on another criteira which tries to match the R output

OTrieger

As you can see whatever you are doing or the right side, you do it with the same data

Alteryxuserhere

Yes But im using different fields to try match what the first Join couldnt. Is this wrong?

OTrieger

As you are using the same data, the one the comes from the Left side after the first Join is the same data without the matches. But still the same data, so the minute that you joining again you are getting Null items on the L, J you will get all the items that comes from L and on R you will have the items that were joined on the first Join

OTrieger

Yes as you are using only the right side data.

If you would like to find additional matches that you did not find in the first time, then you will need to connect the Left side of the first Join to the Left side of the 2nd Join.

OTrieger

Actually it will be even better to Connect the L from 1st Join with the R from the 1st Join. In this way you will find additional matches from the parts that still not matching. The minute that you are taking the original R then you might have duplication that matched on the first time and also on the 2nd time. But I'm not sure exactly what you would like to achieve here.

ChrisTX

@Alteryxuserhere a little suggestion:

identify your data sources going into each Join:
- your "primary" original input A: feed this data into the first Join, Left input
- your secondary input B: feed this data into the first Join, Right input

The records not matched from the first join will be data coming OUT of the first Join, Left output.

For the second Join tool, I think you 'll want to use:

Left input: data coming OUT of the first Join, Left output
Right input option 1: do you want to go back to the original secondary input B? this may create duplicates.
Right input option 2: you likely want to use the data coming OUT of the first Join, Right output.

See example of multiple Join tools, using a "waterfall" approach, here:

Inside Alteryx Designer: Help > Sample Workflows > Use scripting and automation tools > Build a macro > Merge to master file with fuzzy matching
You don't have to use the Fuzzy Match tool, just look at the "waterfall" flow and see where the Left and Right inputs come from
The "waterfall" approach is: try matching using first criteria; for those records that don't match try matching using second criteria

Chris

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235