Separate values in array with ',' ';' or other?

Get Help
1

Hi,

We have a survey app and the way we collect data from a multiple choice question now is by creating for each row an array with each option selected by the respondent separated by a comma.

Example: Which pet do you have at home? 3 options possible (cat, dog, none)
user1_answer = ‘cat’, ‘dog’
user2_answer = ‘cat’
user3_answer = ‘none’

I would like to show the number of time each of my 3 options comes out (cat 50%, dog 33%, none 33%)

However, the way Metabase shows the answer is by creating a 4th option ‘cat, dog’. which returns this: ‘cat, dog’ 33%, dog 33%, none 33%.

However, I don’t want to have that 4th option in my graph.

How can I only show unique options not combined?

2

For your example to get number of people with a cat you can use the contains filter and specify cat and you will btw. arrive at 2 out of 3.

Depending on your data if you got many different options and many of the users also have selected many of the available options simultaneously this method may not be practical though. But I believe it’s a feasible “workaround” if that is not the case.

For a more robust solution consider applying some ETL (extract-transform-load) tool before storing in your database or solve via a raw SQL query. Syntax varies between databases. Here’s a starting point https://stackoverflow.com/search?q=[sql]+split+string+regex