Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row
Hello,
In this scenario, each donation id is a unique value and is 1 row. Each donation id has 2 columns: HGB and HGB2. If HGB is not null, that means the donation id received an HGB stick. If HGB2 is not null, that means the donation id received a second stick. If the fields are null, the donation id recieved 0 sticks. In my attachment "Example1" you can see a visual of the concept I'm going for and what I would want the summary number (beastmode) to return in the scenario.
My first approach was to do a case statement of (when HGB2 is not null, then '2', when HGB is not null and HGB2 is null, then '1', else 'null') and then Sum this case statement. But this did not work because, while donation id is a unique value, there will still be duplications of donation id's within our data (which is necessary for other reasons). And so, even when I would "hide" these duplicate donation id's using a distinct count of donation id, my beast mode in the first sentence of this paragraph would still sum the "hidden" values. I have attached a visual example of this in "Example2"
Finally, I did this beastmode:
case when (case when `lte_donation_general.hemoglobin2` is not null then '2' when `lte_donation_general.hemoglobin` is not null then '1' else `lte_donation_general.hemoglobin` end) = '1' then ((COUNT(DISTINCT`lte_donation_general.donation_id` )) * 1) when (case when `lte_donation_general.hemoglobin2` is not null then '2' when `lte_donation_general.hemoglobin` is not null then '1' else `lte_donation_general.hemoglobin` end) = '2' then ((COUNT(DISTINCT`lte_donation_general.donation_id` )) * 2) end
This is returning all the desired values in my beastmode column. But doesn't quite work and I cannot figure out why. The odd thing is, if I put the HGB2 column in the filter and filter to only donations that received HGB2, I get the correct summary number (beastmode). Attachment "Example3"
But when I take that HGB2 filter out and look at the data as a whole, the summary number only returns the number of donation ids. Attachment "Example4"
My next thought is to do an ETL or some workbench modification, but was hoping to avoid that
Best Answer
-
Have you tried adding the count of distinct donation IDs and adding the two together?
COUNT(DISTINCT CASE WHEN `lte_donation_general.hemoglobin2` IS NOT NULL THEN `lte_donation_general.donation_id` END) + COUNT(DISTINCT CASE WHEN `lte_donation_general.hemoglobin` IS NOT NULL THEN `lte_donation_general.donation_id` END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
-
-
-
Have you tried adding the count of distinct donation IDs and adding the two together?
COUNT(DISTINCT CASE WHEN `lte_donation_general.hemoglobin2` IS NOT NULL THEN `lte_donation_general.donation_id` END) + COUNT(DISTINCT CASE WHEN `lte_donation_general.hemoglobin` IS NOT NULL THEN `lte_donation_general.donation_id` END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Alternatively you could utilize max and a case statement like the following
MAX(CASE WHEN `lte_donation_general.hemoglobin2` IS NOT NULL THEN 1 ELSE 0 END) + MAX(CASE WHEN `lte_donation_general.hemoglobin` IS NOT NULL THEN 1 ELSE 0 END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thank you this worked perfectly.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive