cancel
Showing results for
Did you mean:

# Calculate non unique values

Community Team Member
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?
Solution: 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 neccessary to add this to the datamodel. 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``````
Version history
Last update:
‎03-02-2023 08:34 AM
Updated by:
Contributors
Community Toolbox