Survey results workflow

[helen_c]

Hi all,

I'm using Alteryx to produce a Tableau dashboard for a student survey. So far I've created a workflow that joins together several input files and stacks them all together.

The issue is that each of the input files contains the results broken down by different student characteristics so when these are joined together and stacked on top of each other they are causing me problems! 

I've attached a dummy output file and would love it if someone could offer me some guidance.

Currently I can find the correct survey results for each student characteristic by filtering each of the other characteristics to 'Total'. However this obviously causes problems in Tableau - I need a user to be able to select 'Age' as the characteristic and see the results but the way this output is formatted, I have to filter each of the other characteristics to get the correct result for 'Age'.

Can anyone recommend a better way to structure the data?

(The characteristic fields are columns C-K and the survey measures are columns M-W)

Thanks in advance

[AngelosPachis]

Hi @helen_c ,

I believe it would be really useful to have a mock example of the rough structure of the input files you are joining together, so it's easier to understand what happens in the Join and which fields you should use to join on.

[helen_c]

Hi - thanks for replying. I've attached some input files.

[AngelosPachis]

Hi @helen_c ,

Thanks for attaching those two new mock files. Maybe a silly question but are those to files connected somehow or the survey was anonymous? For example, do we care/is there a way to identify if a respondent participating in 2019 survey also participated in 2020? Or are those two separate files, that we just want to bring together to process in Tableau?

Thanks,

Angelos

[AngelosPachis]

Not sure if that works for you, but my suggestion would be to transpose all columns containing values so you bring your data table in a thin and long format. 

You will have a single measures column called value, and then you can define which value you display on your Tableau dashboard by using the dimensions for measures/name as filters. Another way would be to create calculated fields in Tableau with if statements, so you don't burden your dashboard with the filtering work as much, if your datasource becomes really big.

Then you will notice that for some of your columns (i.e. Age or Gender) you have some nulls and that's because gender is not present in the 2020 survey, whilst Age is not present in the 2019 survey.

[helen_c]

Hi Angelos,

The survey is anonymous so I just need to bring the separate files together to look at trends over time and differences in results between different student characteristics.

Thanks,

Helen

[helen_c]

Thanks - I'll give that a try.

Each of the characteristic fields are present in each years survey but the data comes in 3 different files. So one file for 2019 contains 3 characteristic fields, another file contains a different 3 characteristics and a third contains a further 3 characteristic fields. These are then repeated for other years. 

This is why I put them together in the way I did as I didn't think a join would work properly. I'll give it a try though.

Thanks again