Multiple columns to look up

[hyh]

Hi I'm new to alteryx

So I have 20 columns named Phone 1, Phone 2, Phone 3 etc. and I have one lookup table Phone Number. I need to compare all the phone numbers in Phone 1 column with Phone Number table to see if there is a match. This process goes on for all the 20 columns. I'm wondering if there is an efficient way to do it instead of joining 20 columns with the lookup table one by one.

I really appreciate if you guys can help 🙂

[T_Willins · Accepted solution]

Hi @hyh,

Thank you for providing sample data and a desired output.  It makes it much easier.  While there is more than one way to do this, I started with a RecordID tool so the results could be matched to original data.  I then used a Transpose tool to put all the phone numbers into a single column, then Joined with the lookup table to get the matches; Cross Tab to bring the data back to the original format, Join back to the original data (using the record ID), then Union in any data that did not have any matches.  Finally, Sort by record ID to put everything in the right order.

[hyh]

Hi @T_Willins,

Thank you for your reply. Before I try your method, a quick question, there are other info in my actual file like names, street addresses etc. Will that affect your workflow? Also Match Phone 2 should be Match Phone 3 right? Thanks!

[T_Willins]

Hi @hyh,

First, Match Phone2 and Match Phone 3 are reversed.  I overrode the names in the second Join tool and just reversed them.  If you to dynamically change the names of the matched fields coming out of the Join tool you may want to use a Dynamic Rename tool instead of hard coding the name change in the second Join tool.

For the fields you don't want to change (name, address, etc), deselect them from the Transpose tool (not selected in the Key Column or Data Column)  Those fields will not be in the data after the Transpose tool, but will still be in the original data that comes in through the left anchor of the second Join tool.  The rest of the workflow should work as is, but be sure the fields are in the order you want in the second Join tool.  This avoids having to use a Select tool later in the workflow to reorder the fields.