Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here

Transforming data from vertical to horizontal and grouping?

Highlighted
6 - Meteoroid

Hi all

 

I would love to get your help on a problem I've been trying to solve for the last few days... without any luck.

 

I have been working on a workflow where I'm using a batch macro to ingest a set of data from one specific worksheet (34 lines, sometimes 33 lines) from about 300 questionnaires (Excel files) that are saved in a directory - basically, the responses to the questionnaires.  The data I get from the macro has the following format (I made up some dummy data below as the original data is sensitive). 

 

QuestionResponse
type survey
ratinggreat
score8
namejohn
q1john1
q2john2
q3john3
q4john4
q5john5
q6john6
q7john7
q8john8
q9john9
q10john10
statusfinished
type survey
ratingexcellent
score10
namebud
q1bud1
q2bud2
q3bud3
q4bud4
q5bud5
q6bud6
q7bud7
q8bud8
q9bud9
q10bud10
type survey
ratingpoor
score2
namescott
q1scott1
q2scott2
q3scott3
q4scott4
q5scott5
q6scott6
q7scott7
q8scott8
q9scott9
q10scott10
statusin progress
type survey
ratingaverage
score6
nameemily
q1emily1
q2emily2
q3emily3
q4emily4
q5emily5
q6emily6
q7emily7
q8emily8
q9emily9
q10emily10

 

The first line for each dataset is the same question and always has the same response ("survey") as in the type of questionnaire.  Sometimes the dataset will have 34 lines because there is a "status" field but sometimes it will have 33 lines because the "status" field wasn't captured in the questionnaire.  

 

Ideally, I'd like to transform the data so that the questions are in a horizontal format and each set of response is transposed to rows beneath each question so it can be filtered when outputted to an Excel sheet.  Essentially, I am trying to get the output to look like this:

 

type ratingscorenameq1q2q3q4q5q6q7q8q9q10status
surveygreat8johnjohn1john2john3john4john5john6john7john8john9john10finished
surveyexcellent10budbud1bud2bud3bud4bud5bud6bud7bud8bud9bud10 
surveypoor2scottscott1scott2scott3scott4scott5scott6scott7scott8scott9scott10in progress
surveyaverage6emilyemily1emily2emily3emily4emily5emily6emily7emily8emily9emily10 

 

I've tried using a Tile and Crosstab method but when I checked the results - they were mismatched (e.g. john was showing as having an excellent rating).  I'm not sure if it's because the "status" field isn't always there.

 

I've thought of trying to group each set of record and giving them an identifier e.g. the name since that is different for each response but also not sure how to achieve this since the first question has the same response.

 

Does anyone have any suggestions?  Any help would be much appreciated - thank you!!  

Highlighted
10 - Fireball

Try the attached workflow and see if that gets you where you need to be. The biggest addition is the inclusion of a counter / ID that increments for each survey (regardless on the number of rows). That ID becomes the pivot point for the transpositions. 

 

Hope this helps, and good luck. 

Labels