Lookup for multiple columns against a single list of values

[LJDL]

I have a dataset similar to the sample below;

Data:

Person	GENDER	NATION	AGE	ETHNICITY
1	0	1	5	8
2	3	1	3	8
3	0	0	5	7
4	2	0	1	4
5	3	0	5	7
6	2	1	1	8
7	1	0	4	9
8	0	1	5	8
9	3	1	5	3
10	2	0	2	9

I then have a separate, single lookup file setup like this;

Lookup:

Field	Label	Description
GENDER	0	Female
GENDER	1	Male
GENDER	3	Other
NATION	0	UK
NATION	1	Other (including unknown)
AGE	1	20 and under
AGE	2	21 to 24
AGE	3	25 to 29
AGE	4	30 and over
AGE	5	Unknown
ETHNICITY	1	White
ETHNICITY	2	Black Caribbean
ETHNICITY	3	Black African
ETHNICITY	4	Indian
ETHNICITY	5	Pakinstani
ETHNICITY	6	Bangladeshi
ETHNICITY	7	Chinese
ETHNICITY	8	Mixed
ETHNICITY	9	Other
ETHNICITY	10	Unknown

Which tool(s) can I use to take the appropriate Description and replace the label in the corresponding field in the dataset? The dataset has 35 columns and there are 200+ rows in the lookup table.

Thanks in advance

John

[Luke_C]

Hi @LJDL 

This is a perfect case to use the dynamic replace tool. This tool lets you replace the data based on conditions, in this case the label. See attached example and let me know if you have any questions. 

[LJDL]

Thanks @Luke_C,

when I run this against my full data set I get  Error: Dynamic Replace (13): Parse Error at char(15): Invalid type in operator ==.

It seems to be related to the Formula tool rather than the dynamic replace, if I stick a space before the = in the formula tool the error appears in char(16) and so on.

I've just copied the tools from your example into my model. Any thoughts on the problem? It's reading from an xlsx file if that might cause a problem.

John

[Luke_C]

@LJDL there is probably a mismatch in the data types. You can use a select tool to check and fix. 

If i had to guess the flags in the main dataset are probably coming in as numeric, if you update to string it should work.

[LJDL]

I've tried making every field in both documents a V_String and separately a V_WString but I get the same error message either way.

[pedrodrfaria · Accepted solution]

Hi @LJDL 

I prefer to use the transpose and the crosstab to work with multiple columns. I find it to be much easier.

See the example below using your dataset:

I found some instances where matching the columns variables did not match. For example Row 6 says GENDER = 2, but there is no Gender = 2 in the reference table. Is this correct?

Either way, you should get the understanding of how to approach this using the example provided below.

[Jonathan-Sherman · Accepted solution]

Hi @LJDL,

You could also use the good old transpose attach updated descriptions and crosstab back.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Kind regards,

Jonathan

[Luke_C]

@LJDL  my mistake, make sure the label in the lookup data is numeric, as well as the original data. 

[Jonathan-Sherman]

@pedrodrfaria just be wary that the join method will be case sensitive, this is something you can turn off if using the find and replace method in the technique! 😊

[Jonathan-Sherman]

Nice use case of the Dynamic Replace tool @Luke_C! That's one of the things I love about Alteryx - everyone can take a different approach and still get to the same answer!