Distinct Count with Case statement not working
This is a bit of an odd issue, the below calculation ends up adding 1 when rolling up data:
Distinct count of ID (if Flag=1)=COUNT(DISTINCT CASE WHEN Flag
=1 THEN ID
ELSE 0 END)
ID | Flag | Distinct count of ID (if Flag=1) |
---|---|---|
GRAND TOTAL | 1 | 2 |
456123 | 1 | 1 |
123456 | 0 | 1 |
654321 | 0 | 1 |
Any ideas why this might be happening? This is a mega table with no sort applied.
Best Answer
-
Got it. So I believe it is the ELSE 0 part of your case statement that is causing there to be a 1 where you expect a 0. Because it will count either the ID's if flag =1 or it will count the 0. Either way you will get a 1. I think you're on the right track removing duplicates in ETL - especially if the only function of your beastmode is to "remove" duplicates in the card.
If I solved your problem, please select "yes" above
0
Answers
-
Can you help us understand why the 3rd column is needed? It seems repetitive having the flag column and the Distinct count of ID (if Flag=1) column. Every row in the table card will have a single ID and the flag column is already populated so all you'd really need to do is use the flag column. The distinct count of ID column will always be 1 because there is only ever 1 ID in the ID column in a table card. Maybe I am missing something?
If I solved your problem, please select "yes" above
0 -
Hi! In the data table, there is a possibility of duplicate rows so that's why I've set up the distinct count. This is a helpful call out though, I'm guessing it would be best to remove the duplicates in the ETL vs solve for this in a beast mode?
0 -
Sorry, I may have misunderstood your questions, Iadded the Flag column here just for context so you can see that the value is 0 but the calculation is resulting in 1. I would expect the calculation to result in 0 as well.
0 -
Got it. So I believe it is the ELSE 0 part of your case statement that is causing there to be a 1 where you expect a 0. Because it will count either the ID's if flag =1 or it will count the 0. Either way you will get a 1. I think you're on the right track removing duplicates in ETL - especially if the only function of your beastmode is to "remove" duplicates in the card.
If I solved your problem, please select "yes" above
0 -
Oh my gosh, of course! You are completely right, I removed the ELSE 0 and it's no longer counting the extra. That being said, I'll go back into the ETL and adjust for duplicates there so the beast modes removing duplicates aren't necessary, thanks so much for your insight!
0
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
- 709 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