Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Change orientation of Question Answer Data

tjamal1
8 - Asteroid

Hello,

 

I have a table that has question answers like this

 

QuestionIDTitleAnswerIDAnswer
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?12 Points
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?23 Points
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?34  Points
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?45  Points
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?56  Points
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?67  Points
2Which of these documents are not needed in your vehicle at all times?7Your license
2Which of these documents are not needed in your vehicle at all times?8Proof of insurance
2Which 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 5
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?2 Points3 Points4  Points5  Points6  Points7  Points
2Which 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?

 

3 REPLIES 3
AngelosPachis
16 - Nebula

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

 

AngelosPachis_0-1639591134899.png

 

Cheers,

Angelos

tjamal1
8 - Asteroid

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

QuestionIDTitleAnswerIDAnswerIsCorrect
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?12 Points0
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?23 Points1
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?34  Points0
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?45  Points0
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?56  Points0
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?67  Points0

 

Expected Table

QuestionIDTitleAnswer1Answer2Answer3Answer4Answer5Answer6Correct Answer 
1How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?2 Points3 Points4  Points5  Points6  Points7  Points3 Points

 

 

AngelosPachis
16 - Nebula

Hey @tjamal1 ,

 

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

AngelosPachis_0-1639606813568.png

 

Labels