Using the Join tool incorrectly
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I must be doing something wrong..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There must be records not being matched from the left also, what result does this get?:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, that worked.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Awesome glad it helped !
