Using the Join tool incorrectly

[Adam_B]

Hello Alteryx world, I have 2 data sets that I am using a Join function with, what is am trying to do is join the Right with the Left, but not lose any records from the Left.  So naturally my thought was to use a Join, keep all fields from the LEFT and the one I need from the RIGHT and I should maintain the same number of records.  Well, I am loosing records, which is not what I want.  What am I doing wrong?

Attached is a data set Example1 with Contract ID and ID with 10 records, I want to merge it with Example2 that has 24 records matching on ID and bring the Contract ID field over to the Example2 data set, maintaining the 24 records.  

Thank you for the help as always. 

Adam 

[IraWatt]

Hey @Adam_B,

What you will need to do is this:

As there are ID's which don't match they will be sent to the right input, to bring the data back together you can use a union tool (I've attached an example workflow). 

[NeilParrott]

Hello @Adam_B ,

@IraWatt  is correct. If you are familiar with SQL Joins, placing a join tool and then a union will allow you to correct the "left join" scenarios that you'd expect.

Neil

[Adam_B]

I must be doing something wrong..

[IraWatt]

There must be records not being matched from the left also, what result does this get?:

[Adam_B]

@Ira@Neil Attached are more realistic files.

Example L file has 13K rows of data, which I want to maintain that number, I would like to have Example R blended when the ERP Supplier ID matches on both files, bring back the Contract ID from file Example R to Example L file.  If the row doesn't match it should just be null, if it matches, provide the Contract ID.  Like a VLOOKUP in Excel. 

Thank you for your time. 

Adam 

[IraWatt · Accepted solution]

Hey @Adam_B,

The reason its not acting like a VLOOKUP is because VLOOKUP's take the first value and join on the details onto that. Joins will join on every value that matches. For instance, ERP Supplier ID 1100257 has 183 entries on the left and 5 records on the right each with there own ContactID. This means when they join on ERP Supplier ID each element on the left will join with each element on the right resulting in 915 records out the centre join (this is called a many to many join if you want to look it up). 

If you want to replicate a VLOOKUP then just take the first row for each supplier like this: 

I've attached both workflows 

[Adam_B]

Thank you, that worked. 

[IraWatt]

Awesome glad it helped !