Removing duplicates associated with unique values from another column

johno
johno Member
in Archive

Here's my situation:

 

I am making a card to show survey results, specifically to show response rate.

 

There are 5 questions in the survey and there are currently 16 responses. One of the questions is a multiple choice question that allows for multiple answers.

 

Basically my data table shows a column for question, answer, and id for who said it. One example is that for one unique survey taker, there are 7 rows in the data table. Even though there are only 5 questions, since one question allows multiple responses, the "Question" column looks like it has duplicates. A row would be something like:

 

Q1 -- A1 -- ID1

Q2 -- A2 -- ID1

Q2 -- A2a -- ID1

Q2 -- A2b -- ID1

 

So as you can see it looks like duplicates in the Question column, but unique values in the Answer column, all associated with the same user ID.

 

I want to find the response rate (did someone answer the question or not?). If these duplicates were not an issue, I would just check if the answer column was null to know if the responder answered the question or not. Also, if I just wanted to remove duplicates, that is doable as well. The issue is, I do not want to just remove duplicates, because that would remove the same question for different users. I want to remove duplicates that are associated with a unique ID. That would turn the 4 row example above into 2 rows. And then at that point I can check if the Answer is null in each one to find my solution.

 

Any ideas on how to go about this? Thanks in advance!

Comments

  • ST_Superman
    ST_Superman Domo Employee

    You could do this in a MySQL data flow:

     

    SELECT

    `Question`

    ,Group_Concat(`Answer` SEPARATOR ' | ') as `Answer`

    ,`ID`

    from table

    GROUP BY `Question`,`ID`

     

    This should give you an output like this for each user:

     

    Q1 -- A1 -- ID1

    Q2 -- A2 | A2a | A2b -- ID1

    ...

     

Categories