topic Re: How to add field Base By previous Data in Data Movement & Streaming

How to add field Base By previous Data

elmelavi — Wed, 19 Mar 2025 23:06:21 GMT

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

LAVI

Re: How to add field Base By previous Data

sidhiq91 — Thu, 02 Feb 2023 16:52:31 GMT

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

Re: How to add field Base By previous Data

elmelavi — Fri, 03 Feb 2023 04:47:48 GMT

Thank u very much

It's exactly what I looked for

Re: How to add field Base By previous Data

sidhiq91 — Fri, 03 Feb 2023 09:39:32 GMT

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

Re: How to add field Base By previous Data

elmelavi — Tue, 07 Feb 2023 20:25:12 GMT

liked