Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Using the Join tool incorrectly

Adam_B
8 - Asteroid

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 

8 REPLIES 8
IraWatt
17 - Castor
17 - Castor

Hey @Adam_B,

What you will need to do is this:

IraWatt_0-1650896715569.png

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
8 - Asteroid

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
8 - Asteroid

I must be doing something wrong..

 

Adam_B_0-1650901845381.png

 

IraWatt
17 - Castor
17 - Castor

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

 

IraWatt_0-1650902078672.png

 

Adam_B
8 - Asteroid

@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
17 - Castor
17 - Castor

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). 

IraWatt_1-1650920908131.png

 

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

IraWatt_2-1650921168447.png

I've attached both workflows 

 

Adam_B
8 - Asteroid

Thank you, that worked. 

IraWatt
17 - Castor
17 - Castor

Awesome glad it helped ! 

Labels
Top Solution Authors