Lookup Multiple Columns of Data

[anveshg]

Hello all,

I am currently working with CRM Data from a database where the contact data looks as follows.

Contact ID	Contact Name	Contact Gender	Contact Country	Contact Industry
1	A	1	0	3
2	B	1	2	2
3	C	2	1	2

Several attributes in the table are encoded and the lookup data is stored in a different table as follows.

Lookup Type	Key	Value
Gender	0	Other
Gender	1	Male
Gender	2	Female
Country	0	X
Country	1	Y
Country	2	Z
Industry	0	Value1
Industry	1	Value2
Industry	2	Value3
Industry	3	Value4

I am trying to extract several contact records from the table and would need to translate around 14 attributes with a lookup table structured as above.  I am currently doing it through a chain of find replaces and joins. However, I am unsure if this is the best way to go forward. 

Any ideas to achieve this in a better way?

[OllieClarke · Accepted solution]

Hi @anveshg you can use the dynamic replace tool to do this

First you'll want to set up your lookup table like this:

And then set up the dynamic replace tool like this:

Which will then give you this output:

I've attached the workflow below. Hope that helps,

Ollie

[benakesh]

Hi @anveshg ,

With transpose  you can use one  find replace .

[anveshg]

Thank you! I will give it a run.