topic Re: Flagging duplicates in QlikView

Flagging duplicates

Wed, 27 Jan 2016 04:08:58 GMT

I'm importing data from a QVD and want to add a column so duplicates are flagged.

When the Supplier_Name and Product_Code combination is the same, flag all subsequent entries with the same combination with 1. Product_Status will be used to sort the data.

My data looks like this;

Supplier_Name	Product_Code	Product_Status
A Pty Ltd	1ABC	A
A Pty Ltd	1ABC	I
B Pty Ltd	2ABC	A
C Pty Ltd	3ABC	A
C Pty Ltd	3ABC	I
D Pty Ltd	4ABC	A
D Pty Ltd	5ABC	A

Re: Flagging duplicates

tamilarasu — Wed, 27 Jan 2016 06:47:14 GMT

Hi Michael,

You can try something like below,

Temp:
Load *,
Supplier_Name&Product_Code as Key;
LOAD * INLINE [
Supplier_Name, Product_Code, Product_Status
    A Pty Ltd, 1ABC, A
    A Pty Ltd, 1ABC, I
    B Pty Ltd, 2ABC, A
    C Pty Ltd, 3ABC, A
    C Pty Ltd, 3ABC, I
    D Pty Ltd, 4ABC, A
    D Pty Ltd, 5ABC, A
];

Join
Load Key,
If(Count(Key)>1,1,0) as Flag
Resident Temp Group by Key ;

Output:

Re: Flagging duplicates

jagan — Wed, 27 Jan 2016 06:53:22 GMT

HI,

Try like this

Data:

LOAD

FROM DataSource;

LEFT JOIN(Data)

LOAD

1 AS Flag

WHERE Count > 1;

LOAD

Supplier_Name,

Product_Code,

Count(Supplier_Name & Product_Code) AS Count

RESIDENT Data

GROUP BY Supplier_Name, Product_Code;

Re: Flagging duplicates

PradeepReddy — Wed, 27 Jan 2016 07:11:57 GMT

see the attachment, it might be helpful..

Edit:

If you are reading the data from QVD use bellow script...

Temp_QVD:

Load *

From QVD_File.qvd(qvd);

order by Supplier_Name, Product_Code, Product_Status;

Suppliers:

Load *,

if(Supplier_Name=peek(Supplier_Name) and Product_Code=peek(Product_Code), '1','0') as Flag;

Load *

resident Temp_QVD order by Supplier_Name, Product_Code, Product_Status;

Drop Table Temp_QVD;

Re: Flagging duplicates

settu_periasamy — Wed, 27 Jan 2016 07:45:12 GMT

Hi Pradeep,

Just to Mention, We can't use the 'Order by' Clause in the QVD Source.

Re: Flagging duplicates

PradeepReddy — Wed, 27 Jan 2016 07:53:12 GMT

Yes, we can't use 'order by', i over looked that one...