Optimize multiple vlookups

[amadra]

Hello community,

I have a dataset with 28 fields and half a million rows.
Within my dataset, I have 6 fields which I need to lookup in another table (simple lookup to get another field).

I can solve this using a couple of different ways:

• Combination of transpose and cross-tabs (will be an expensive operation for 28 fields and half a million rows)
• Use 6 'Join' tools (This is what I did for the attached sample workflow)
• Use 6 'Find and replace' tools

Question - Is there a tool in Alteryx which will do the lookup in a better/optimized way?

In my example - Field 1, 5, 10, 15, 20, 25 contain names which I lookup to find emails from the second table.

I found a post from 2019 with the same use case but with no solution.

[amadra]

Anyone?

[phottovy]

Hi @amadra ,

I don't have a great solution that doesn't use at least one cross tab. Here is my example:

This does seem like it could be a great topic for a weekly challenge. If you submit it as a topic, you will get a lot more eyes on the problem and I guarantee a lot of creative responses from the community.

[amadra]

@phottovy  - thanks for creating a potential solution.
Your solution gives one column for email after summarize tool, but I'll need emails (after lookup) for Field1, 5,10,15,20 and 25.

Let's see what others have to say.

[phottovy]

@amadra 

Gotcha, now I see where the transpose comes in. Here is a slightly modified version.

[Qiu]

@amadra 
I have a feeling maybe an Iterative Macro should work, but need more time to check 😁