Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

Reply
Highlighted
Yellow Belt

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

 

 


Accepted Solutions
Purple Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Tags (1)

All Replies
Highlighted
Yellow Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

Not sure why only 1 of my attachments posted

Highlighted
Yellow Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

 
Highlighted
Yellow Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

 
Purple Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Tags (1)
Highlighted
Purple Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**
Tags (1)
Highlighted
Yellow Belt

Re: Beastmode Trouble - Getting a unique count of 2 based on 2 columns in 1 row

Thank you this worked perfectly.   

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!