How to use supporting reference table to convert numbered codes into plain English values

[AlsTricks]

Hi everyone,

I've got a puzzle that I'm sure must be straightforward enough for experienced users to solve.

I have a survey response data file where text values (e.g., 'Strongly agree', 'No' and the like) are coded as numbers (e.g. 1, 2, 3). The data is presented horizontally and each row comprises the responses of an individual.

I also have supporting table that includes the names of the fields, the number codes and the plain English values for what the number codes mean. The data is presented vertically.

I would like get Alteryx to replace the number codes in the response data file with the plain English values by referring to the supporting table (for example, if a value for a particular field is coded as 1 and 1 means 'Yes' for that field, then 'Yes' will replace the 1). This will make the data easier to work with in Tableau.

I would like to avoid using the find-replace tool (because this can only handle one field at a time) and the multi-field formula (because this can only handle the fields where the codes mean the same thing).

Would you kindly be able to advise me on whether this is possible in Alteryx?

I thought about whether an IF statement with two conditions could work. In Excel, this would be something like =IF((AND(A2=A2,B2=B2)),C2,"NULL").

Thank you very much for your help,

Alex

[grossal · Accepted solution]

Hi @AlsTricks,

Here is a way to achieve this:

What happens:

1) Record ID to preserve the order

2) Transpose to shift the data

3) Join the data base on column name and value (and remove not needed columns)

4) Cross Tab to shift the data back

Workflow attached. Let me know if this is what you are looking for.

Best

Alex

[AlsTricks]

Hi Alex,

You're a legend - thanks so much!

Alex