Hello, I have a table that has question answers like this QuestionIDTitleAnswerIDAnswer1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?12 Points1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?23 Points1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?34 Points1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?45 Points1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?56 Points1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?67 Points2Which of these documents are not needed in your vehicle at all times?7Your license2Which of these documents are not needed in your vehicle at all times?8Proof of insurance2Which of these documents are not needed in your vehicle at all times?9Registration papers.2Which of these documents are not needed in your vehicle at all times?10Vehicle Instruction manual I want to transform it to this form so that each question and its answers exist in one line/record. QuestionIDTitleAnswer 1Answer 2Answer 3Answer 1Answer 4Answer 51How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?2 Points3 Points4 Points5 Points6 Points7 Points2Which of these documents are not needed in your vehicle at all times?Your licenseProof of insuranceRegistration papers.Vehicle Instruction manual How can I do this in Alteryx Designer?

Change orientation of Question Answer Data

Hello,

I have a table that has question answers like this

QuestionID	Title	AnswerID	Answer
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	1	2 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2	3 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	3	4 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	4	5 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	5	6 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	6	7 Points
2	Which of these documents are not needed in your vehicle at all times?	7	Your license
2	Which of these documents are not needed in your vehicle at all times?	8	Proof of insurance
2	Which of these documents are not needed in your vehicle at all times?	9	Registration papers.
2	Which of these documents are not needed in your vehicle at all times?	10	Vehicle Instruction manual

I want to transform it to this form so that each question and its answers exist in one line/record.

QuestionID	Title	Answer 1	Answer 2	Answer 3	Answer 1	Answer 4	Answer 5
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2 Points	3 Points	4 Points	5 Points	6 Points	7 Points
2	Which of these documents are not needed in your vehicle at all times?	Your license	Proof of insurance	Registration papers.	Vehicle Instruction manual

How can I do this in Alteryx Designer?

Transformation

Preparation

Accepted answers

AngelosPachis

Hey @tjamal1 ,

You can do that by filtering out all the correct answers and joining back to the main table

sample_workflow.yxmd

All comments

AngelosPachis

Hi @tjamal1 ,

Here's how you can do it with a cross tab tool; the trick is to count how many columns you have per question, which you can do with a multi-row formula tool

Cheers,

Angelos

sample_workflow.yxmd

tjamal1

Thank you @AngelosPachis, that worked perfectly fine.

I have a question my original table has one field which flags answers as True or False. Is there any way to add another column in the last (to your solution) so that Correct Answer will be in the last column called "Correct Answer".

Example

Original Table

QuestionID	Title	AnswerID	Answer	IsCorrect
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	1	2 Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2	3 Points	1
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	3	4 Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	4	5 Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	5	6 Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	6	7 Points	0

Expected Table

QuestionID	Title	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Correct Answer
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2 Points	3 Points	4 Points	5 Points	6 Points	7 Points	3 Points

AngelosPachis

Hey @tjamal1 ,

You can do that by filtering out all the correct answers and joining back to the main table

sample_workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703