How can I relate multiple data points for the same entry?
Hello,
We have a data set that allows users to select more than one data point.
How can I format the data so that we can pivot around the responses?
Simplified example:
Question #4:
There are some numbers below. Select as many as you would like.
Possible Answer Choices:
A:1
B:2
C:3
D:4
The response could be “A, B, D”. Or “A, D”. Or “A,B,C,D”, etc.
So our data object would have
[
{//response object
...
Question3: "A", //Different question with answer response.
Question4: "A,B,D", //Multiple answer responses.
Question5: "B" //Different question with answer response.
...
}
]
Multiple Q4’s won’t work since it just uses the last one. We’d like to be able to say “How many selected A?” or “If they picked Question4 A, what did they pick for Question5?” etc.
Is it possible to correlate data? If we formatted the data so that it ended up with Question4.1, Question4.2, Question4.3, etc, could the pivot table be able to recognize all of those as one response so that it can be used as a column/row value?
Thanks,
-Mike
1 answer
Hi Mike,
Thank you for posting your question.
In order to achieve the desired data analysis possibilities, we would advise rearranging your data in a way the each question input is stored in a separate JSON entry.
For example, this entry:
{
"UserID": 913,
"Question1": "A",
"Question2": "A,B"
}
would be represented by something like this:
{
"UserID": 913,
"Question#": 1,
"Input": "A"
},
{
"UserID": 913,
"Question#": 2,
"Input": "A"
},
{
"UserID": 913,
"Question#": 2,
"Input": "B"
}
We’ve prepared a quick CodePen example in order to demonstrate this approach: https://codepen.io/webdatarocks/pen/poJPNPz
Please let us know if this helps.
Best regards,
WebDataRocks team