Removing duplicates associated with unique values from another column
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
-
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
...
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive