Add multiple field label 'look ups' from a flat file

[SHamilton1]

Hi,

I have a file with about 150 fields of data (columns) and a row per ID. Some fields contain codes which map to a text label (others don't). I have the text labels in a separate csv file but this is flattened data and all the codes are stacked. Is there anyway of joining the two files and adding the labels as new fields at the end of the original file (see below for a simple example). I am trying to avoid doing 150 separate joins and I am sure there must be a better (much more efficient) way using transpose or another tool but I don't know how.

ID	Gender	Colour	Age_group
ID_1	1	1	1
ID_2	2	2	3
ID_3	1	3	2

FieldName	Code	Label
Gender	1	Male
Gender	2	Female
Colour	1	Blue
Colour	2	Pink
Colour	3	Yellow
AgeGroup	1	Under 20
AgeGroup	2	20-30
AgeGroup	3	Over 30

ID	Gender	Colour	Age_group	Gender_Label	Colour_Label	AgeGroup_Label
ID_1	1	1	1	Male	Blue	Under 20
ID_2	2	2	3	Female	Pink	Over 30
ID_3	1	3	2	Male	Yellow	20-30

[Emil_Kos]

Hi,

If ID and Code are the same columns you can use simple join tool to join them together but according to your merged table that is not the case as ID_3 should have label over 30.

Can you clarify if ID is equal to code? 

[SHamilton1]

Hi,

No ID is not equal to Code. ID is the unique key for each row of data. ID_3 has an Age_Group code of 2 which maps to '20-30'.

I could do individual joins for each of the fields to bring in their respective labels but as there are about 150 fields which need a label bringing in this seems like a long-winded of doing it.

[ImadZidan · Accepted solution]

Hello @SHamilton1 ,

Have a look at the attached. just an idea.

Hope it helps.

[SHamilton1]

This is perfect thank you!