Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kimginkey
New Contributor

Counting repeated rows

Hi. Say I have a table of data as below and I would like to count the number of rows that are duplicated (i.e. all 4 columns are the same).

NameCol BCol CMonth-Yr
JaneA1Jan-17
JaneA1Jan-17
JaneA1Jan-17
JaneA1Jan-17
JaneA1Jan-17
PeteB2Jan-17
PeteB2Jan-17
PeteB1Jan-17
KennyD1Feb-17
LayllC2Feb-17
LayllC2Feb-17
MitchelleE4Feb-17
KayD1Feb-17

For the case above, number of repeated rows should be 6 ('Jane, A, 1, Jan-17' was duplicated 4 additional times; 'Pete, B, 2, Jan-17' duplicated once; 'Layll, C, 2, Feb-17' duplicated once, so total 6).

Anyone can assist with the expression I should input on Qliksense?

4 Replies

Re: Counting repeated rows

‌May be this:

Sum(Aggr(If(Count(Name&[Col B]&[Col C]&[Month-Yr]) > 1, 1, 0), Name, [Col B], [Col C], [Month-Yr]))

Re: Counting repeated rows

Alternatively, you can combine the four fields into one and do this

LOAD AutoNumber(Name&[Col B]&[Col C]&[Month-Yr]) as Key

     ....

FROM ....


and then this

Count({<Key = {"=Count(Key) > 1"}>}Key)

ChennaiahNallani
Contributor III

Re: Counting repeated rows

Create resident tables with left join. so we have DuplicateFlag field take has a sum(DuplicateFlag) you will get count of duplicate


left join (TableName)

load FieldName, if(count(FieldName)>1,1,0) as DuplicateFlag

Resident TableName

group by Date, FieldName;

ChennaiahNallani
Contributor III

Re: Counting repeated rows

Composite key -- FieldName

Community Browser