Create new item (position) field in script

[jorditorras]

Hi Gurús, 

I have a data model based on information of orders where I have one record per order (I was counting orders doing  "1 as ordercounter"). However now I'm doing a left join to another table and now I have multiple records per order. 

I'd like to create in the script an extra field (something like a "position line") that generate a position counter. Something like: 

Order      Position Line

OR001    001

OR001    002

OR002    001

OR003    001

OR003    002

OR003    003

...

Any idea of how I can generate this field "Position Line"?

Thank you very much!!!

[Vegar]

You could try something like I've done in my script below.

TMP:
LOAD * inline [
Order, OrderDate
OR001, 2020-10-10
OR002, 2020-10-12
OR003, 2020-10-13
OR004, 2020-10-13];

LEFT JOIN 
LOAD * inline [
Order, Article, Qty
OR001, Apple, 	100
OR001, Orange,	90
OR002, Apple,	200
OR002, Orange,  250
OR003, Pineapple, 50
OR003, Orange,  300
OR003, Banana,	125
OR004, Banana,  260
];

Final:
LOAD 
	Order,
	OrderDate, 
	Article, 
	Qty,
	if(peek('Order')=Order, num(Peek('Position Line')+1, '000'), 001) as [Position Line]
Resident TMP
Order By Order;
DROP TABLE TMP;

[Taoufiq_Zarra · Accepted solution]

@jorditorras  like ?

if(rowno()=1,1,if(peek("Order")="Order",peek([Position Line])+1,1)) as [Position Line]

example :

load *,if(rowno()=1,1,if(peek("Order")="Order",peek([Position Line])+1,1)) as [Position Line] inline [
Order
OR001
OR001
OR002
OR003
OR003
OR003
];

output:

[jorditorras]

Thank you so much!! That works perfectly!!!!!!!!

[jorditorras]

I'm seeing that it's not working for all the records.... There are some specific orders with duplicated "Position Lines":

[jorditorras]

Now it's done. I was just missing Vegas suggestion of Orderding by Order. Thanks to both!!!