topic Re: Report to Identify Duplicates in QlikView

Report to Identify Duplicates

Wed, 28 Oct 2015 23:00:09 GMT

I have some duplicates records that I need to identify and report.

The criteria is to check several fields if they have the same values (there are able 8 fields to check, Date, Customer, Procedure Time, Manager Assigned, etc.)

What is the best way to do this? Thank you in advance.

Re: Report to Identify Duplicates

swuehl — Wed, 28 Oct 2015 23:23:26 GMT

So you want to check for records that show identical values in all 8 fields?

Maybe like this (example for 4 fields)

LOAD recno() as RecID,

Date,

Customer,

[Procedure Time],

[Manager Assigned],

Hash256(Date, Customer,[Procedure Time], [Manager Assigned]) as Key

FROM ...;

Then create a list box for field Key and enable frequency option. Also sort by frequency descending.

Create a table box with fields RecID, Date, [Procedure Time] and [Manager Assigned] (add other fields from the table as you like).

Select Keys with a frequency >1.

Re: Report to Identify Duplicates

Wed, 28 Oct 2015 23:30:06 GMT

Thank you for your reply. What does Hash256 do?

Re: Report to Identify Duplicates

Anonymous — Thu, 29 Oct 2015 05:52:40 GMT

Hi,

Make a key using the same combination of fields using autonumber or such functions and put them in a list box with frequency checked on. Select keys with freq.>1

HTH

Re: Report to Identify Duplicates

jagan — Thu, 29 Oct 2015 06:09:08 GMT

Hi Melissa,

Try like this

Chart : Straight Table

Dimensions: Date, Customer, Procedure Time, Manager Assigned, etc.

Expression: If(Count(Customer) > 1, 'Duplicate', Null())

Hope this helps you.

Regards,

jagan.

Re: Report to Identify Duplicates

swuehl — Thu, 29 Oct 2015 15:52:41 GMT

It justs calculates a unique value per combination of arguments to the function (well, with theoretical limitations of uniqueness).

Instead the Hash256() function, you can also use Autonumber() or just a concatenation, like

Customer & '-' & [Procedure Time] &'-'&[Manager Assigned] as Key

What Jagan suggested is also a good solution, without the need to create a Key in the data model, here the key is created by using all the fields as dimensions.