How to check previous entries in a column

[poonam_kulwal]

Hi All,

I have a table with Product_ID and Batch_ID. I want to create a Status column that has information regarding if Product_ID is getting repeated or not based on Batch_ID.

Product_ID	Batch_ID	Repeat Status
AA11	1234	No
AA11	1235	Yes
AA11	1238	Yes
AA11	1256	Yes
AB23	1111	No
AR32	2141	No
BE32	3553	No
SD87	244	No
AA11	4554	Yes
AR32	2454	Yes

I want to generate Repeat status column in my load script.

Any help will be appreciated.

Thanks & regards,

Poonam

[sunny_talwar]

May be like this

LOAD Product_ID,

     Batch_ID,

     If(Product_ID = Previous(Product_ID), 'Yes', 'No') as [Repeat Status]

Resident ....

Order By Product_ID, Batch_ID;

[poonam_kulwal]

Thanks Sunny. I also added RowNo() function in order to keep rows on same position.

Wondering My Product_ID and Batch_ID are alphanumeric fields with very long string.

example "jsfsa3242lkklmsk456j5jnksdfk000000000042320jkk23" like this.

and my qvd size is 1 GB.

Wondering, "order by" will work fine in terms of optimization concern?

Or do I convert my alphanumeric field to something.

Table1:

LOAD RowNo() as Rowno,
  Product_ID,
     Batch_ID       
FROM (ooxml, embedded labels, table is Sheet1);

Table2:
load *, if(previous([Product_ID])=[Product_ID],'Yes','No') as [Repeat Status]
Resident Table1 Order by Product_ID, Batch_ID ASC;
DROP table Table1;

[sunny_talwar]

If you can correctly sort the data within your Excel file, then you don't have to use the order by clause. Order by is used to make sure that the unsorted data is sorted correctly. Now if you don't have it sorted correctly, there is no better way to do this

[Monicalingan]

Hi Poonam,

Inline:

LOAD * INLINE [

   Product_ID, Batch_ID,

    AA11, 1234

    AA11, 1235

    AA11, 1238

    AA11, 1256

    AB23, 1111

    AR32, 2141

    BE32, 3553

    SD87, 244

    AA11, 4554

    AR32, 2454

];

Load * ,  if(Product_ID=peek(Product_ID),'Yes','No') as RepeatStatus

Resident Inline

Order by Product_ID;

Drop table Inline;

Hope this helps.

Thanks,

Monica

[poonam_kulwal]

Thanks Sunny.