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

[atcodedog05]

Hi @Hannah33swain 

The file provided is it data file or mapping file ? and can you provide the other file(sample data). So that we have both files to understand the usecase.

[Hannah33swain]

Thank you! 

Please see attached the updated file.

I effectively need the output to show the following:

1) Person Numbers split out to show whether they are red/green - if there is a way to flag which column in the mapping file is incorrect that would be fantastic

2) Show codes/countries/business groups which are missing from the mapping files

Also to flag - for Audit/Legal we want to look at this at business level 1 only as it does not matter what there business group 2 is for mapping purposes. Is this possible please?

Thanks so much!

Hannah

[atcodedog05 · Accepted solution]

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 🙂