How to add field Base By previous Data

[elmelavi]

Hay lts say I have that Data:

ITEM ID	SUPLAYDATE	QTYTOSUPLAY	INVENTORY
123	10/02/2023	10	15
123	18/02/2023	3	15
456	18/03/2023	6	9
456	25/03/2023	2	9

and i need to add leftover column that considerate the item ID only

so i will have that table:

ITEM ID	SUPLAYDATE	QTYTOSUPLAY	INVENTORY	LEFT OVER
123	10/02/2023	10	15	5
123	18/02/2023	3	15	2
456	18/03/2023	6	9	3
456	25/03/2023	2	9	1

I HAVE TO DO IT IN THE SCRIPT

BR

LAVI

[sidhiq91 · Accepted solution]

@elmelavi  Please see the code below. 

NoConcatenate
temp:
Load ITEMID,
Date(date#(SUPLAYDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as SUPLAYDATE,
QTYTOSUPLAY,
INVENTORY,
rowno() as Row_number

inline [
ITEMID,SUPLAYDATE,QTYTOSUPLAY,INVENTORY
123,10/02/2023, 10, 15
123,18/02/2023, 3, 15
456,18/03/2023, 6, 9
456,25/03/2023, 2, 9
];

NoConcatenate
Temp1:
load ITEMID,

Date(date#(SUPLAYDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as SUPLAYDATE,
QTYTOSUPLAY,
INVENTORY,

if(Row_number='1', INVENTORY-QTYTOSUPLAY,
if(ITEMID=peek(ITEMID) ,peek(leftover)-QTYTOSUPLAY,INVENTORY-QTYTOSUPLAY) )as leftover

Resident temp;

Drop table temp;

Exit Script;

If this resolves the issue, please like and accept it as a solution.

[elmelavi]

Thank u very much

It's exactly what I looked for

[sidhiq91]

@elmelavi  Glad that it worked for you. Could you please like the solution as well. Thanks in advance.

[elmelavi]

liked