Alteryx Designer

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

Alteryx - matching data - excel Vlookup

Highlighted
7 - Meteor

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @WL1, 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.

Highlighted
Alteryx
Alteryx

Hey @WL1 , 

 

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. 

 

Capture.PNG

Highlighted
Alteryx
Alteryx

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.

Highlighted
7 - Meteor

thanks, I was able to use this to get to where I wanted

Highlighted
7 - Meteor

thanks

Highlighted
7 - Meteor

Hi

thanks for that, didn't use this way this time but if have problems in the future will give this way a go

Labels