topic Re: Duplicates in QlikView

Duplicates

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I've been struggling to delete duplicates from my Qlikview pivot table. Because each of my line items have a host of issues ranging from simple spelling mistakes through to misplaced string values.

Say for example I have line items with the following fields and :

ID Employee_Number Responsible

910330 ABC123 Pravir Ishvarlal

910330 ABC123 Ishvarlal Pravir

910330 ABC123 P Ishvarlal

Is there a way to delete these duplicates by "rolling-up" these records into 1 line based on the same ID or Employee_Number key, say:

ID Employee_Number Responsible

910330 ABC123 Pravir Ishvarlal

Kind Regards,

Pravir

Re: Duplicates

Anil_Babu_Samineni — Mon, 07 Aug 2017 15:54:57 GMT

You must changed records to this?

LOAD ID, Employee_Number, If(Match(Responsible, 'Ishvarlal Pravir','P Ishvarlal'),'Pravir Ishvarlal', Responsible) as Responsible Inline [

ID, Employee_Number , Responsible

910330 , ABC123 , Pravir Ishvarlal

910330 , ABC123 , Ishvarlal Pravir

910330 , ABC123 , P Ishvarlal

];

Re: Duplicates

vishsaggi — Mon, 07 Aug 2017 16:03:55 GMT

Try this :

Picked from one of Sunny's expressions using Previous function.

LOAD *

WHERE RecNum = 1;

LOAD *, IF(ID = Previous(ID), RangeSum(Peek('RecNum'), 1), 1) AS RecNum INLINE [

ID, Employee_Number, Responsible

910330,ABC123, Pravir Ishvarlal

910330,ABC123, Ishvarlal Pravir

910330,ABC123, P Ishvarlal

910335, ZZZ222, S Surya

910335, ZZZ222, Setti Surya

910335, ZZZ222, Surya Setti

];

Re: Duplicates

effinty2112 — Tue, 08 Aug 2017 16:02:45 GMT

Hi Pravir,

I added a new field to your data to sum over:

Data:

LOAD * Inline [

ID, Employee_Number, Responsible, Sales

910330, ABC123, Pravir Ishvarlal ,20

910330, ABC123, Ishvarlal Pravir, 34,

910330, ABC123, P Ishvarlal, 23];

Now create a pivot table with one natural dimension and two calculated dimensions:

=aggr(FirstSortedValue(DISTINCT Employee_Number, ID),ID) //label this Employee_Number

=aggr(FirstSortedValue(DISTINCT Responsible, ID),ID) //label this Responsible

The measure is sum(Sales)

ID	Employee_Number	Responsible	sum(Sales)
910330	ABC123	Pravir Ishvarlal	77

Hope this helps

Andrew

Re: Duplicates

antoniotiman — Tue, 08 Aug 2017 16:13:03 GMT

Hi Pravir,

LOAD ID,[Employee_Number],FirstValue(Responsible) as Responsible
Group By ID,[Employee_Number];
LOAD * Inline [
ID , Employee_Number, Responsible
910330, ABC123, Pravir Ishvarlal
910330, ABC123, Ishvarlal Pravir
910330, ABC123, P Ishvarlal];

Regards,

Antonio