How to do split when the order in the data-to-be-split is not defined?

Question

I am trying to visualize a question-answer (multiple-choice) through tableau. all the data is in one column of the table in json format like

{"q1":"1","q3":"3","q2":"2"}

I want to plot q1, q2, q3 against the answers. q1, q2, q3 could be in any order in the json in different rows of the table. AN example of such table is:

User_id       Answer
1         {"q1":"1","q3":"1","q2":"2"}
2         {"q2":"3","q3":"2","q2":"2"}
3         {"q3":"1","q1":"4","q2":"1"}

I think I should extract q1 and its value and make a separate column for this. Similarly for q2 and q3. But I am not sure how to do such conditional split when the order is not fixed. How can I do this?

For analyzing survey results with Tableau, start with http://www.datarevelations.com — Alex Blakemore, Nov 13 '18 at 01:21

score 0 · Answer 1 · answered Nov 13 '18 at 21:36

This is a scenario where regular expressions can help. Create a calculation like below(Just replace question number as required)

REGEXP_EXTRACT([Answer],'"q1":"(\d)')

This regex assumes the exact formating provided in question, but you can improve it to allow single quotes, white spaces,etc.Please refer to this answer if you need help.

Note: q1 is missing in second row and it returns null.

How to do split when the order in the data-to-be-split is not defined?

1 Answers1