topic Re: Create new item (position) field in script in App Development

Create new item (position) field in script

jorditorras — Mon, 20 Dec 2021 21:08:46 GMT

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!!!

Re: Create new item (position) field in script

Vegar — Tue, 13 Oct 2020 08:24:31 GMT

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;

Re: Create new item (position) field in script

Taoufiq_Zarra — Tue, 13 Oct 2020 08:31:14 GMT

@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:

Re: Create new item (position) field in script

jorditorras — Tue, 13 Oct 2020 11:08:53 GMT

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

Re: Create new item (position) field in script

jorditorras — Tue, 13 Oct 2020 14:18:30 GMT

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

Re: Create new item (position) field in script

jorditorras — Tue, 13 Oct 2020 19:06:08 GMT

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