Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
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
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
Thanks Sunny.