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

Jbrorby

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

 

 

GrantSmith

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)

Jbrorby

Not sure why only 1 of my attachments posted

Jbrorby

Jbrorby

GrantSmith

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)

GrantSmith

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)

Jbrorby

Thank you this worked perfectly.