Hi, I'm quite new to Alteryx and have to do a report about a survey by using the software.
I'm not sure of how to summarize the data based on the answers.
Some Context:
For each question the answer goes from 0 to 10, if the value is between 0 and 6 is categorized as "BAD", if its 7 or 8 "REGULAR" and if its 9 or 10 "GOOD"
Survey ID | Question 1 | Question 2 |
0001 | 6 | 10 |
0002 | 10 | 9 |
0003 | 2 | 7 |
0004 | 7 | 5 |
With the Formula option I created a new table with the correct label using "IF THEN ELSEIF ELSE END"
Survey ID | Question 1 | Status | Question 2 | Status |
0001 | 6 | Bad | 10 | Good |
0002 | 10 | Good | 9 | Good |
0003 | 2 | Bad | 7 | Regular |
0004 | 7 | Regular | 5 | Bad |
Now I need to summarize the results for each question using percentages. Eg. For the Question 1, 50 % is "Bad", 25 % is "Good" and 25 % "Regular".
Which tool should I used? I tried summarize and failed... hopefully i explained myself. Thanks 🙂
Solved! Go to Solution.
Hi @federicohernandezd ,
I think you should first try to restructure your data so you have your data extending vertically and not horizontally. That means, having a single column for survey ID, a single column for the question ID and the answer in that question (1-10) and then the flag of whether it's good or bad. I believe that will help you process the data now but also in the future.
The you can use a summarize tool to count the number of answers per question and in total.
Hope that helps, let me know if you have any questions.
Regards,
Angelos
Hi @federicohernandezd ,
I'm a big fan of making these kinds of problems entirely dynamic, as they often grow out of hand (i.e. the formulas get more & more complex).
They way I would do that is by having a reference table (lookup table) that contains the upper & lower boundaries of the value you wish to assign, e.g. 0 (lower value) and 6 (upper value) with the result field containing the result (Bad). That way, if new permutations come in, e.g. "above average", you can simply add it to the table.
I've taken the liberty of stealing @AngelosPachis , starting workflow (I agree with him on the transposing of rows) - but just adding the reference tables to this.
I wasn't 100% sure on how you wanted to assign values & result scores, but hopefully when you run the attached workflow, you'll understand the concept I'm referring to.
Feel free to ask questions of course!
Tom
Hi @AngelosPachis, thank you very much for the reply
With your example I finally understood Transpose and Crosstab tools, also, I will take your advice of processing the data vertically.
I just have one further question, in the "Summarize" you add a column named "Value" which isn't in the options, how come? I add a screenshot of the configuration tool for "Summarize"
Thanks!
Hey @tomwelgemoed, I find this approach quite interesting. Although the result I'm trying to get is different, this workflow is really flexible. I'll keep in mind using those formulas in the filters to simplify the amount of objects. New on Alteryx but willing to learn from those tips. Thank you very much!
Hi @federicohernandezd ,
Yes, thanks for the feedback - indeed, it's really good when you want to create more dynamic & scalable workflows and you don't want to hard-code your formulas (but rather just map values directly).
Good luck on your Alteryx journey!
Hi @federicohernandezd ,
That's a fair question, probably that was the artefact of some testing I was doing while building the workflow.
You can ignore it and substitute it with Question ID. So the configuration of the top summarize tool should be the following
and for the one below
Happy that this solution help you understand better Transpose and Cross Tab tools and sorry if this inconvenience with that Value field confused you.
Cheers,
Angelos
Great!!
Thank you very much