Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;