Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Transpose/Cross Tab one column

jgiacomazzi
7 - Meteor

I'm consolidating survey results and my input data is in the following format:

 

SectionQuestion numberQuestionResponse
11.01Placeholder 1Company 1 response - 1.01
11.02Placeholder 2Company 1 response - 1.02
11.03Placeholder 3Company 1 response - 1.03
11.04Placeholder 4Company 1 response - 1.04
11.05Placeholder 5Company 1 response - 1.05
11.01Placeholder 1Company 2 response - 1.01
11.02Placeholder 2Company 2 response - 1.02
11.03Placeholder 3Company 2 response - 1.03
11.04Placeholder 4Company 2 response - 1.04
11.05Placeholder 5Company 2 response - 1.05
11.01Placeholder 1Company 3 response - 1.01
11.02Placeholder 2Company 3 response - 1.02
11.03Placeholder 3Company 3 response - 1.03
11.04Placeholder 4Company 3 response - 1.04
11.05Placeholder 5Company 3 response - 1.05

 

I would like to get in the following output format:

SectionQuestion numberQuestionResponse 1Response 2Response 3
11.01Placeholder 1Company 1 response - 1.01Company 2 response - 1.01Company 3 response - 1.01
11.02Placeholder 2Company 1 response - 1.02Company 2 response - 1.02Company 3 response - 1.02
11.03Placeholder 3Company 1 response - 1.03Company 2 response - 1.03Company 3 response - 1.03
11.04Placeholder 4Company 1 response - 1.04Company 2 response - 1.04Company 3 response - 1.04
11.05Placeholder 5Company 1 response - 1.05Company 2 response - 1.05Company 3 response - 1.05

 

(I don't care much about the header names as I can always update them later), but essentially I'm asking how I can keep the first 3 columns and then transpose the response column to be separate for each company's response. I know it's probaby either the transpose or cross tab tool, but I haven't used them in a while so I'm rusty and confused.

 

Caveats:

 

- Some of the company responses have commas within the response, so cross tab and parsing by commas won't work

- In some cases, the first 3 columns have the same exact wording, which causes confusion because it looks like Alteryx combines them (see the "Details" row below)

 

Example: 

SectionQuestion numberQuestion
11.01Question 1a
11.01Details
11.01Question 1b
11.01Details
7 REPLIES 7
DataNath
17 - Castor

How does the below solution look? I just manually added a 'Response #' field but it'd be easy enough to do this dynamically (i.e. parse or assign a unique ID to each company) in your live workflow. I didn't quite understand your caveats so if this doesn't work then let me know, give a bit more context (perhaps with a screenshot or data snippet where I can recreate it) and I'll have a rethink!

 

DataNath_0-1651098502651.png

 

jgiacomazzi
7 - Meteor

Thank you so much! This would definitely work if I had the response # field. Can you show me how you would create it using parse or assigning a unique ID? The response itself likely wouldn't have "company 1", "company 2", etc. in it.

DataNath
17 - Castor

Are you able to show a small sample of how responses look? Just so I can see how we might go about differentiating between different companies/groups of respondents. Even if you just mock up something close to the real data!

jgiacomazzi
7 - Meteor

Just attached sample of responses. Thank you for your help!

DataNath
17 - Castor

Awesome, thanks for that!

 

I've attached a workflow containing a solution below. Hopefully this is acceptable for what you're looking for as there is a slight 'Q num' modification in there to allow for differentiation when necessary.

 

This also assumes that the whole dataset follows this format!

 

DataNath_0-1651102519994.png

 

fharper
12 - Quasar

your sample data has no identifier for the responder, in a separate field or embedded in the response. 

 

In surveys we often do not get to know who said what... but we often have responses that duplicate, 1,000 people surveyed and 400 prefer Red, 300 prefer Blue, 150 prefer Green and 150 prefer yellow...

 

To expand on the work by @DataNath  a couple Summarization tools to get the count of a given response within the key structure or question and total responses to a question and run with it to show not only the unique or top N unique and their count or %Responded...etc.

 

I did do cleanup on the test data to show multiples of a response and to align question# to be unique per question text

 

For some reason I can't upload data or workflows so I am showing some screen shots...sorry

 

fharper_0-1651111204540.png

 

fharper_1-1651111246662.png

 

fharper_2-1651111300172.pngfharper_3-1651111329396.png

 

fharper_4-1651111402882.png

 

 

flying008
14 - Magnetar

Hi,@jgiacomazzi 

 

your require like other we answered post yesterday.

 

录制_2022_04_28_14_18_15_440.gif

 

******************

As your data of Sample survey data2.xlsx

录制_2022_04_28_14_29_41_818.gif

Labels