Hey! Are you staying home? We are too. Still, we continue working on our product to support you with a free solution.

We hope it comes in handy for your business especially these days. Stay safe & pivot on!

How can I relate multiple data points for the same entry?

Answered

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

WebDataRocks Team WebDataRocks February 28, 2020

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

Please login or Register to Submit Answer