Calculate non unique values

[Community_Admin]

Question:

I have an example of data below. I want to be able to get a count of the Unique Member ID's that have more than 1 different confirmation number tied to it. So in the simple example below that number would be 1. Member 55555 has 3 entries, two with the same confirmation number and 1 with a different confirmation. I want to be able to create a flag in the data to flag every time a unique member ID has multiple confirmation numbers tied to it and then be able to count how many of those unique member ID's there are in the data set. How can I accomplish this?

Answer:

In order to add a field to the datamodel which tells you if a specific member ID has 1 or >1 confirmation numbers and know of those member ids that have multiple numbers, how many are unique.

You can solve this on the front-end in a pivot (however it does not make it possible to add a filter on dashboard level). In the Pivot-widget add:

• member ID on a row level
• DUPcount(confirmation number) on value level
• count(confirmation number) on value level
• DUPcount(confirmation number) - count(confirmation number) on value level
• Filter on the last formula to only show >0 

If you want to have a field (e.g. flag) to use on dashboard level, then it is necessary to add this to the data model. You could add a custom query like;

SELECT c.*, 
case 
when count(c.member_id) > 1 then 1
else 0
end 
as Flag
from confirmation_table c
group by c.member_id

You can replace 1 and 0 with Yes/No or True/False. Place them between '' 

After this you can add the flag as dashboard filter. Create a widget that does a unique count on confirmation number. 

Say you want multiple flags regarding uniqueness;

SELECT c.*, 

//Flags how many confirmations a member has 
case when count(c.member_id) > 1 then 1 else 0 end as Flag 

//Flags if that member_id has duplicates regarding conf.number
case when count(c.confirmation_number) - count(distinct c.confirmation_number) = 0 then 0 
else 1 end as Flag_2

from confirmation_table c
group by c.member_id