Flagging duplicates

[#1]

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

[tamilarasu]

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:

[jagan]

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;

[PradeepReddy · Accepted solution]

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;

[settu_periasamy]

Hi Pradeep,

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

[PradeepReddy]

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