Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nikita42
Partner - Contributor III
Partner - Contributor III

How to use aggr to get count of values associated to a duplicate?

Hi, I have a table like this:

CaseIDCity
1ANY
2BNY
2CNY
3DLON
4ENY
4FNJ
5GLON

I've managed to get the count of cases that have multiple IDs associated to it using the expression:

Count(If(Aggr(Count(ID),Case)>=2,Case)

and this gives me the correct result - 2

Now I need to get the count of IDs and cities associated with these cases. Basically I need to show that these 2 cases are duplicated across 4 people.

I've tried changing the expression to Count(If(Aggr(Count(ID),Case)>=2,ID), but this still gives me the count of cases because the aggr table doesn't provide a row for each ID.

Similarly, I need to get a count of the number of unique cities that have a duplicate case. In this example only case 4 has 2 distinct cities, so I need the result to be 2.

Any ideas?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Or maybe, if you want to exclude case 2 (which in this case shows same City then case 4, but could be different):

=Count(DISTINCT {<Case = {"=Count(DISTINCT City) >= 2"}>} City)

View solution in original post

5 Replies
sunny_talwar

May be this:

Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} ID)

UPDATE: Sample attached

nikita42
Partner - Contributor III
Partner - Contributor III
Author

That works correctly for the ID count (Thanks!)

But the distinct city count is still off.

sunny_talwar

=Count(DISTINCT {<Case = {"=Count(ID) >= 2"}>} City)

swuehl
MVP
MVP

Or maybe, if you want to exclude case 2 (which in this case shows same City then case 4, but could be different):

=Count(DISTINCT {<Case = {"=Count(DISTINCT City) >= 2"}>} City)

nikita42
Partner - Contributor III
Partner - Contributor III
Author

Yes, That works!

Thanks