How to convert row categories into columns & replace row values with other column values?

Question

Hello,

Am very new at Alteryx. Thank you for all the help in advance.

I have data that looks like the following:

customer_idquestionanswer_chosen1Q112Q253Q324Q145Q73

1) I am trying to convert the unique questions into column categories, and 2) replace the row values of the new columns with the respective answer_chosen value, else leave it empty or null, and then 3) append the new columns to the end of the original data set.

Results should look like the following:

customer_idquestionanswer_chosenQ1Q2Q3Q4Q5Q6Q71Q111      2Q25 5     3Q32  2    4Q14 1      5Q73      3

And the real data I have is very massive with more questions than above example.

Could you please help point me in the right direction?

Thanks,

~Sky

danrh · Accepted Answer

You can do this with the Cross Tab tool:

Group by all your fields, select "question" for column headers and "answer_chosen" for values. The only issue I see is if you need to populate those questions that aren't present in the data (Q4-Q6 in your data). Will every question be present in your real data set? If so, this one tool can handle this.