Join/union duplicating records

Question

Hello.

hoping someone can help.

im trying to connect a username table to a target data table,

joining by the username. The hierarchy has different variations of the username with their IDs. Example:

Mike Smith   #22309

Michael Smith #22309

with some having as many as 5 different variations of their name. The problem I’m having is I have the target table that has about 300,000 records and out of those only 120,000 have that username to match up to, the other 180,000 are just blank and I want the username and ID to remain blank for those 180,000. So I want to just append to those 120,000 records by matching by the username to get the user IDs. end up with 300,000 records when all done.

When I try to use the join and union function, or even add in the unique function I end up with over 2million records.

any help is greatly appreciated!!

brandonstilley · Answer

This helped me a ton! I was performing a join and union to add back in the record that fell out, and this took care of it easily for me. In some cases I had 5-7 duplicates and it was super frustrating!

echuong1 · Answer

An increase in records means there is a many-to-many relationship, which will cause what's called a cartesian join. It will essentially output all combinations, which is why you'll get more records than you started with.

I recommend using fuzzy matching, since you can have name variations in both of your datasets and this may lead to a mismatch. In the example below, I started by creating a list of every username in both tables with a Union. I then used a Fuzzy Match and Make Groups to get every name in the list, and the variations of it. The Key corresponds to the variations, and the Group is what we want them to be normalized to. I then used a Find and Replace on both datasets to look for variations of the keys, and replace the value with the normalized group. To ensure I didn't have the variation issue, I used a Unique on the ID value. I then used a Join to bring over the ID values.

New Workflow1.yxzp

mhey01 · Answer

Hey @J054305,

Looking at your image, it looks like you need to include the 'Unique' tool or a 'Summarise' tool after your first join (illustration below).

Would it be possible for you to share your workflow with us?