Duplicates

[#1]

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

[Anil_Babu_Samineni]

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

];

[vishsaggi]

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

];

[effinty2112]

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:

ID

=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

[antoniotiman]

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