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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 633 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 62 App Studio
- 42 Variables
- 699 Automate
- 181 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive