Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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?

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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
Highlighted

Re: How to use aggr to get count of duplicate values?

May be this:

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

UPDATE: Sample attached

Highlighted
Partner
Partner

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

That works correctly for the ID count (Thanks!)

But the distinct city count is still off.

Highlighted

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

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

Highlighted
MVP
MVP

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

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

Highlighted
Partner
Partner

Re: How to use aggr to get count of duplicate values?

Yes, That works!

Thanks