Cumulative Stock Value

[vskanberg]

Hello,

I'm trying to create a projected stock value for all articles in our system.
I've combined 3 tables. 
Current Stock,
Inbound Stock & Outbound Stock.
Each table has Different Article Numbers, Quantities & Dates. 
//The 'Current Stock Table' have no Date, but I've assigned it Todays Date on all rows.

I'm trying to create the current stock for each date moving forward with consideration of the inbound & outbound stock per article for each date.

I've attempted several methods using Peek, Previous & Mapping but I've been unsuccessful with my desired result.
See attached example file in Excel + the Code in Notepad.
Here is my desired result and my current data:
[DESIRE] is what I want. 
Article, Date, Stock, In, Out is what I have currently.

Article	DATE	STOCK	IN	OUT	DESIRE
Article1	2023-06-08	100	0	0	100
Article1	2023-06-09	0	0	0	100
Article1	2023-06-10	0	0	0	100
Article1	2023-06-11	0	0	0	100
Article1	2023-06-12	0	10	0	110
Article1	2023-06-13	0	0	0	110
Article1	2023-06-14	0	0	0	110
Article1	2023-06-15	0	0	-10	100
Article1	2023-06-16	0	0	0	100
Article1	2023-06-17	0	10	0	110
Article2	2023-06-08	50	0	0	50
Article2	2023-06-09	0	0	0	50
Article2	2023-06-10	0	5	0	55
Article2	2023-06-11	0	0	0	55
Article2	2023-06-12	0	0	0	55
Article2	2023-06-13	0	0	-50	5
Article2	2023-06-14	0	0	0	5
Article2	2023-06-15	0	0	-50	-45
Article2	2023-06-16	0	10	-10	-45
Article2	2023-06-17	0	100	0	55

[Gabbar · Accepted solution]

As you mentioned :-
You have Article, Date, Stock, In, Out.
Do the process to combine them in 1 table, lets Suppose that Table is TableA.
Then in script try this:-
TableA:
Load * from TableA;

NoConcatenate
B:
Load *,If(Article=Previous(Article),(Peek(Desire)+IN-OUT),STOCK) as Desire
Resident TableA order by Article Asc, DATE Asc;

Drop Table TableA;