Mapping tables

Hannah33swain

Hi,

I am hoping for some assistance please. I need to create a table in Alteryx where I can check a data set against a mapping table to establish "Green" (correct) and "Red" (incorrect) options. Ideally the mapping table would be an excel input file so when this is updated it would feed through to the workflow.

The table would be as per the attached.

Any advise would be greatly appreciated.

Thanks,

Hannah

Example - Mapping file.xlsx

atcodedog05

Hi @Hannah33swain 

Here is how you can do it.

Workflow:

1. Since Business level 2 for Audit & Legal is blank. I am creating a column Business level 2 for dataset where for Audit & Legal its blank and others it takes SubLob.

2. On mapping table i am converting code columns to rows. So that its easier to compare.

3. Use join tool, i am joining on company code, business L1, business L2 & country. If the codes and info matches then it comes through join (J anchor). The codes in mapping table which doesnt match info comes through right unjoin (R anchor) here for all Non null (i,e green) set value as red since they are wrong.

4. In left unjoin (L anchor ) you are getting  codes/countries/business groups which are missing from the mapping files.

5. Using crosstab converting mapping table columns into table.

Hope this helps 🙂

codes.yxmd