Identify and mark duplicate records in load script

[jagjivvt]

Hi 

I need to mark each duplicate record in load script with an incremental number. 

InvoiceID, Mark

1 , 1

1, 2

2,1

2,2

3,1

and so on. 

thanks

[LReeve · Accepted solution]

Hi Jagjvvt,

Try something like this:

tmpTable:

Load InvoiceID,

1 As Mark

From File;

NoConcatenate

FinalTable:

Load

InvoiceID,

If(InvoiceID = Previous(InvoiceID), Peek('Mark') + 1, Mark) As Mark

Resident Table

Order By InvoiceID;

You may need to also sort by a datetime value to mark the invoices correctly.

[jagjivvt]

Thank you