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!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #50: Preparing Survey Data

GeneR
Alteryx Alumni (Retired)

The link to last week’s challenge is HERE.

 

Happy-Thanksgiving-Cliparts-animated.gif

 

Happy thanksgiving to the USA based challenge participants, hopefully you are hungry for another exercise.  This week we will continue with some more data parsing and preparation with survey data.  Enjoy!

 

Use Case:  The 2 sources of data contain survey information. The Data input contains all of the survey responses. The Questions input contains the associated questions.

 

The column (called Column) in the Questions file corresponds to the field header value in the Data file, so value 38 in the Questions files is the questions associated with field F38 in the Data file.

 

The first row in the Data file contains the response type. In field F38, the data is formatted as Response (Scale 1-10) - Age Range.

 

Objective:  Create an output file for a visualization tool (Tableau, Qlik, PowerBI) that details the response by age for each individual.

dataMack
12 - Quasar

If you need some hints on how survey data would be used in a a tool like Tableau, I highly recommend checking out Steve Wexler's blog- he's written extensively about dealing with and visualizng survey data:

http://www.datarevelations.com/category/visualizing-survey-data-and-likert-scales

 

MichelKars
6 - Meteoroid
Spoiler
Weekly #51.PNG
Joe_Mako
12 - Quasar
Spoiler
I took this exercise a bit further and parsed all the data for Tableau, and made some Tableau dashboards from some of the more interesting non-admin questions.

I added two additional Text Inputs, one for identifying question kinds (Single Choice, Multi-Choice, Linkert, etc) along with Question to Open Ended pairings also denotes if the Linkert scale is reversed, and one for identifying fields to become filterable Dimensions in Tableau.

The "Column" values in the "Questions" Input did not mact the actual field order in the "Data" Input. A Formula tool with the expression:

"F"+ToString(IF [Column]<89 THEN [Column]+10 ELSE [Column]-85 ENDIF) 

this corrects the "Column" value.

The workflow adds RecordID to the "Data" Input, Transposes to make the data tall, Filters on RecordID=1 to Join back in the Label, uses Join Multiple to bring in attributes of the questions, then to parse the Response filters out the question kinds that need parsing, and additionally splits out the ID/Values for the multi-choice responses with multiple selections making use of the Join by record position to bring back together, Unions all the responses back together, pulls out the fields to be made into Dimensions with a Join tool, and Cross Tabs to create the additional fields, then Join Multiple to bring the data back in, and Outputs to a .tde file.

Week 51.png

Since we cannot currently attach workflows, here is a link to it:
https://dl.dropboxusercontent.com/u/72203/Weekly%20Exercise/Week%2051%20Data_Prep_SurveyParsing.yxmd

Here is the published Tableau workbook, with some basic views of the different styles of questions:
https://public.tableau.com/profile/publish/Week51_0/LinkertSytleQuestions
TaraM
Alteryx Alumni (Retired)

A solution has been posted

Spoiler
2016-11-28 10_57_19-Alteryx Designer x64 BETA - Data_Prep_SurveyParsing_Solution.yxmd.png
Tara McCoy
SeanAdams
17 - Castor
17 - Castor

good challenge

 

Spoiler
Used text formulas and Text to Column instead of Regex to split the values

Other than that - very similar to posted solution
NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution! 

 

Spoiler
WeeklyChallenge50.JPG
estherb47
15 - Aurora
15 - Aurora

50 solutions!!

 

Had to fix the data (qs 28 and 38 were reversed in the Questions source)

Otherwise, same solution as many. Wish that the questions matched the Data so that I could have cleaned all of it up for vizzes.

SeanAdams
17 - Castor
17 - Castor
AWESOME! You're probably only the 3rd or 4the person to hit the 50 Mark!

@JoeM - this looks like it is going to be an epic sprint to be first to cross the 75 Mark. Do you think we can get live coverage of this on some Extreme Sports show? Just 7 more weeks to go, if my brain is working right....

JoeM
Alteryx Alumni (Retired)

@SeanAdams, I'll submit a request for a live feed for Challenge #75 to be broadcast on ESPN 8 - "The Ocho".  :)