Challenge #50: Preparing Survey Data

[GeneR]

The link to last week’s challenge is HERE.

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.

[RWvanLeeuwen]

Pretty doable, but the field selection was the hardest part as I was reading through the long names. I should have used the prefix I generated to filter columns very early in the process.

Spoiler

[chetty]

My solution, bit of a long winded way but got there in the end

[FRANCK_ROUQUETTE]

Thank you for the exercise - attached my solution

[Hannah_Lissaman]

My solution I think the hardest part was tracking down the correct questions due to the data issue!

[SophiaF]

Select!

Spoiler

[TonyA]

Not one of my better efforts. I got the result, but the workflow was very purpose-built for the data. I plan on coming back to this one.

[jagdeep_singh85]

PFA my solution

[ken_yap_au]

My solution attached.

[Si-Pri]

RegEx is an area I need to develop. Thank you to the community for pointing me in the right direction.

[SubratDas5]

My Solution