I am working on developing a Quicksight dashboard using Market Research data which holds a few multiple-response questions i.e. one field for each response option for example Q15_1 (Option A), Q15_2 (Option_2) etc. similarly Q16 has Q16_1, Q16_2, Q16_3. And I want to provide pivot table where these response options should be shown as rows. in the similar fashion as shown in attached snapshot.
What would be the best approach to achieve this? One way is to unpivot data for Q15, q16 and join it with main dataset however unable it duplicates the records and impacts the numbers.
The easiest way to achieve the desired view in a pivot table would be to add a grouping column to your dataset that assigns each row to a specific question number. So for instance, where you have ‘Q15_1’, ‘Q15_2’, etc.
Instead you’ll have a Question column, which would be ‘Question 15’ for each of the rows that are currently ‘Q15_#’.
With this, then you can have one designated answer column that holds the above strings. So in Quick Sight, you would add your Question field, followed by your answer field underneath so that it expanded to have each of these answer options underneath each question.
If each question’s responses are currently setup with their own column, you’ll be unable to place in a pivot table as at some point, you’ll hit a limit on fields you can add (and it will not populate correctly).
Thank you for your response. This solves one of my problems, other issue is with Q16 i.e. when I join Q16 data; Q15 numbers go up. What could be the issue?
Hi @jitendral.yadav,
What type of join are you utilizing currently?
Additionally, can you share a little more information about each dataset, the fields that each contain and how you’re performing the join?
So the data file has 1 row per record where q15 has one field per option (fixed) and I want to show it in a pivot table with Options as rows and columns for counts and %s.
What I did is created a separate data file for q15 and unpivot it to have 3-column data (see image below) and then join it with main data set on responseid (left join).
Apart from this I have another question q16 in same format as q15 (which is subset of q15) i.e. if an Option is selected at q15 then only it can be selected at q16.
Now the final visual is something like as shown below.
Just checking back in since this thread hasn’t received a response in a while. Were you able to solve your other question regarding the Q16 join or is the issue still persisting? If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
