Find overlapping or duplicate records in QLik

[Qlikuser225]

Hello All,

Can anyone please help me to identify duplicate records in Qlik

For example:

ID, Date, VisitBank , VisitReason
AAA, 12/23/2023, JP , ACCTOPEN
AAA, 12/23/2023, JP , ACCTOPEN
AAA, 12/23/2023, JP , LOAN
AAA, 01/23/2024, JP , LOAN
BBB, 12/14/2023, BO , ACCTOPEN
BBB, 12/14/2023, BO , LOAN
CCC, 12/30/2023, WF , LOAN
DDD, 02/98/2024, WF , LOAN

Here i want to identify  below records since ther are duplicate

AAA, 12/23/2023, JP , ACCTOPEN
AAA, 12/23/2023, JP , ACCTOPEN 

Thanks in Advance

[PrashantSangle]

Just add all those column in table and then add one measure as  count(1)

where value is greater than 1 are duplicate

Regards,

Prashant Sangle

[steeefan]

Depending on the size of the data set, it might be faster to calculate a hash value for the whole row (see Hash128()). You would then just GROUP BY the hash values and, as suggested, COUNT() them and look for results >1;

[sidhiq91]

@Qlikuser225  Please use the below code to resolve the issue. 

NoConcatenate
Temp:
Load ID,Date,VisitBank,VisitReason

Inline [
ID, Date, VisitBank , VisitReason
AAA, 12/23/2023, JP , ACCTOPEN
AAA, 12/23/2023, JP , ACCTOPEN
AAA, 12/23/2023, JP , LOAN
AAA, 01/23/2024, JP , LOAN
BBB, 12/14/2023, BO , ACCTOPEN
BBB, 12/14/2023, BO , LOAN
CCC, 12/30/2023, WF , LOAN
DDD, 02/98/2024, WF , LOAN
];

NoConcatenate
Temp1:
Load *
where Dup_value>=2;

Load *, count(1) as Dup_value
resident Temp
group by ID,Date,VisitBank,VisitReason;

Drop table Temp;

If this resolves your issue, please like and accept it as a solution.