Alteryx - matching data - excel Vlookup

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

Accepted answers

NicholasM

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.

TonyA

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.

All comments

AbhilashR

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.