Alteryx - matching data - excel Vlookup
- 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
Hi
I am trying to do something I usually do with vlookup in excel. I have tried to use the join tool but am not getting exactly what I want.
I have two sheets, sheet 1 and sheet 2.
In sheet 1, I have two columns, in column A there are material numbers, in column B there are commodity codes which correspond to the material number in column B.
In sheet 2, there are more columns with other product data such as weight, value etc, but there are also a column for material numbers and another column with the corresponding commodity codes.
However, some of the commodity codes are missing in sheet 2.
I want to match the material number column in sheet 1 with the material numbers in sheet 2, and for the corresponding commodity code in sheet 1 to appear in the relevant blank cell in sheet 2. Is there a way to do this using join tool or any other tool on Alteryx? I am sure there is but have been able to get hang of it thus far.
Thanks
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BillyL14, the usage of Union tool right after the Join should probably help you get what you are looking for. Connect the L and J output of the Join tool with the Union and see if it gives you what you are looking for. If not, I request you to provide us some mocked up dummy data for us to help put together a proof of concept.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @BillyL14 ,
I would recommend looking at the tool Find Replace located in the Join Tool tab. This should achieve the functionality you are looking for.
The set up would look something like the photograph below, with the "Find Within Field:" and the "Find Value" field being Material number from your respective data sets. Make sure you choose the option append fields to record, and check any additional columns you want to add or "Vlookup".
For more information please check out the Vlookups for Designer in our Academy under Interactive Lessons.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I agree with @NicholasM that Find Replace is the first choice but if your lookup table is very large you might consider the Join Multiple as well. It would be more efficient for combining larger data sets.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thanks, I was able to use this to get to where I wanted
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi
thanks for that, didn't use this way this time but if have problems in the future will give this way a go
