Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
May be this:
Sum(Aggr(If(Count(Name&[Col B]&[Col C]&[Month-Yr]) > 1, 1, 0), Name, [Col B], [Col C], [Month-Yr]))
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)
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;
Composite key -- FieldName