topic Re: Table to display duplicate values only in App Development

Table to display duplicate values only

julie_mita — Mon, 02 Mar 2020 11:10:43 GMT

Hi all,

have been trying a way to filter the data in a table but have not been successful.

My table looks like the below:

I need the table to display only records with two rows or more i.e. rows for Key Field 000111 and 000112.

Have tried a few things with no luck in the data handling section such as:

if(Aggr(count( [Key Field]),[Key Field])>1,1,0)

Do you have any suggestions?

Re: Table to display duplicate values only

jochem_zw — Mon, 02 Mar 2020 11:21:01 GMT

KeyField as dimension

and as expression:

if(count(KeyField)>1, 1,0)

Re: Table to display duplicate values only

BalaBhaskar_Qlik — Mon, 02 Mar 2020 12:06:01 GMT

Try the script like this:

ABC:
Load
          [Key Field],
          Date,
          [Key Field]&Date as [New Key Field],
         Age,
         Count([Key Field]) as Counts
from abc
           group by [Key Field];

Inner Join (ABC)

BCD:
Load
         [Key Field],
         Sum(Counts) as Frequency
Resident ABC
                  Group by [Key Field];

NoConcatenate

Duplicate_Check:
Load
         *,
          If(Counts > 1, 'Duplicate Records','No Duplicates') as [Duplicate Flag]
Resident ABC
                   Order by [Key Field];

Drop table ABC;

NoConcatenate

OnlyDuplicates:
Load
*
Resident Duplicate_Check
Where Match([Duplicate Flag],'Duplicate Records');

Drop table Duplicate_Check;

Re: Table to display duplicate values only

julie_mita — Tue, 03 Mar 2020 00:44:07 GMT

Thanks for your reply Jochem.

Have tried this solution and it did not work unfortunately.

Tried converting the field to number for both dimension and measure but still no luck 😞

Re: Table to display duplicate values only

julie_mita — Tue, 03 Mar 2020 02:04:08 GMT

So have tried this solution and it also fails... I did not mention but the table is dynamic as it is driven by a date picker filter. Depending on the selection the results will vary.

Do you have any other suggestions?