How to do multiple lookup from values in 1 column to another?

Question

Hi Team,

I am new to Alteryx and currently working on a problem that I am not able to find a solution to. So the problem is like this:

Table 1:

A large dataset with millions of rows having the below columns of interest:

IDManufacturer1x-13_ManufacturerA_2552Z-24_ManufacturerB_55342_ManufacturerC410_ManufacturerD5ManufacturerA6ManufacturerB

Table 2: It is an input table wherein I would put the list of Manufacturers for which I need the data from Table 1:

ManufacturerManufacturerAManufacturerBManufacturerCManufacturerD

I need the final output like this.

IDManufacturer1ManufacturerA2ManufacturerB3ManufacturerC4ManufacturerD5ManufacturerA6ManufacturerB

So the heart of the problem is: Table 1 is a very large dataset having a column of Manufacturer name. Now, this column was filled manually and thus have random extra characters leading or trailing. I want to get the data filtered from Table 1 for the n number of Manufacturers which I can insert in the input file Table 2. Had there been only 1 manufacturer data I needed then I would have simply applied "Contains" formula to create a flag. But here there are multiple manufacturers needed.

Please let me know the solution for this. I tried some but as the table is big it would become very heavy to run.

Thanks in advance!

OllieClarke · Accepted Answer

Hi @USER132991 
This is a great usecase for the find and replace tool. If you set it up as so (in append mode)

Then drop the original manufacturer column, and rename manufacturer2

You should have what you need.

Hope that helps,

Ollie

grazitti_sapna · Accepted Answer

Hi @USER132991 , i guess find and replace tool will be the best approach to use in such use case.

Please refer to the workflow and screenshot.

I hope this helps.

Thanks.