Counting repeated rows

[Anonymous]

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).

Name	Col B	Col C	Month-Yr
Jane	A	1	Jan-17
Jane	A	1	Jan-17
Jane	A	1	Jan-17
Jane	A	1	Jan-17
Jane	A	1	Jan-17
Pete	B	2	Jan-17
Pete	B	2	Jan-17
Pete	B	1	Jan-17
Kenny	D	1	Feb-17
Layll	C	2	Feb-17
Layll	C	2	Feb-17
Mitchelle	E	4	Feb-17
Kay	D	1	Feb-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?

[sunny_talwar]

‌May be this:

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

[sunny_talwar]

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]

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]

Composite key -- FieldName