Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
ClementByr
New Contributor III

How to count duplicate by categories ?

Hello,

I trying to count duplicate by categories in my data, here is an example :

IncidentMachineNumSerie
A11
A12
B11
A11
C21
D21
B21
D22
F23
D22
D31
D32
G33
G33
A41
G42

 

In this data, I want to count duplicates per machine. The expected result is suposed to be :

IncidentMachineNumSerieDuplicates
A113
A123
B111
A113
C211
D213
B211
D223
F231
D223
D312
D322
G332
G332
A411
G421

 

Can someone help me achieve this results, please ?

Best regards.

Labels (3)
1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: How to count duplicate by categories ?

try this expression:

aggr(nodistinct count(Machine),Incident,Machine)

View solution in original post

3 Replies
Frank_Hartmann
Honored Contributor II

Re: How to count duplicate by categories ?

try this expression:

aggr(nodistinct count(Machine),Incident,Machine)

View solution in original post

ClementByr
New Contributor III

Re: How to count duplicate by categories ?

Franck,

Thank you for the quick reply.

I tried your formula but it did not work. I am supposed to get the value 2 for the first two A, because there two A Incident duplicate on machine 1.

duplicate.png

Best regards.

Edit : After rechecking the results, the formula is the good one. But in the table, Qlik Sense have erased the duplicated rows that share the same value for Incident, Machine and NumSerie.

Frank_Hartmann
Honored Contributor II

Re: How to count duplicate by categories ?

Yes, I know.

that is why i added rowno()  as Row to my test script and used Row as a further dimension!