Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There must be someone who has done this before.
I need to create stock information for ever date, in the script, but I only got the begin amount and the changes.
I think it is easiest to explane with an example.
Here is an example of what I have and want:
I have the changes
Product | 30-10-2012 | 31-10-2012 | 1-11-2012 | 2-11-2012 | 3-11-2012 | 4-11-2012 | 5-11-2012 | 6-11-2012 |
AAA | 20 | 1 | -1 | 33 | 5 | 6 | ||
AAB | ||||||||
AAC | 100 | 3 | ||||||
BBB | 1 | 3 | -10 | 5 | 0 | 22 | 0 | 45 |
BBC | 23 | 3 | -2 | -6 | ||||
CCC | 3 | 4 | -5 | 12 |
This is the result I am looking for:
Product | 30-10-2012 | 31-10-2012 | 1-11-2012 | 2-11-2012 | 3-11-2012 | 4-11-2012 | 5-11-2012 | 6-11-2012 |
AAA | 20 | 21 | 20 | 20 | 55 | 55 | 60 | 66 |
AAB | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
AAC | 100 | 103 | 103 | 103 | 103 | 103 | 103 | 103 |
BBB | 1 | 4 | -6 | 1 | 1 | 23 | 23 | 48 |
BBC | 23 | 26 | 26 | 26 | 24 | 24 | 18 | 18 |
CCC | 0 | 3 | 7 | 7 | 7 | 2 | 2 | 14 |
So for every day I want to have the amount that is in stock based on the changes.
What is the way to do this in the script?
Thanks,
Dennis.
Hi Dennis,
You can try this code in script
If(Date > Previous(Date), RangeSum(Data, Peek('DateNew')), Data) AS DateNew
Regards,
Tom
Thanks Tom , but that doesn't get any results....
I am a bit closer now I think because I have sorted the data in the right way now.
What I got now is data like this:
Product ID | date | Amount |
AA | 30-10-2012 | 4 |
AA | 31-10-2012 | |
AA | 1-11-2012 | |
AA | 2-11-2012 | 6 |
AA | 3-11-2012 | |
AA | 4-11-2012 | -3 |
AA | 5-11-2012 | |
AA | 6-11-2012 | |
BB | 30-10-2012 | 1 |
BB | 31-10-2012 | |
BB | 1-11-2012 | 100 |
BB | 2-11-2012 | |
BB | 3-11-2012 | |
BB | 4-11-2012 | -25 |
BB | 5-11-2012 | |
BB | 6-11-2012 | 3 |
What I want is:
Product ID | date | Amount |
AA | 30-10-2012 | 4 |
AA | 31-10-2012 | 4 |
AA | 1-11-2012 | 4 |
AA | 2-11-2012 | 10 |
AA | 3-11-2012 | 10 |
AA | 4-11-2012 | 7 |
AA | 5-11-2012 | 7 |
AA | 6-11-2012 | 7 |
BB | 30-10-2012 | 1 |
BB | 31-10-2012 | 1 |
BB | 1-11-2012 | 101 |
BB | 2-11-2012 | 101 |
BB | 3-11-2012 | 101 |
BB | 4-11-2012 | 76 |
BB | 5-11-2012 | 76 |
BB | 6-11-2012 | 79 |
Any suggestions?
Thanks,
Dennis.
You can use peek to calculate the Amount field
LOAD [Product ID],date,
if(rowno()=1,
Amount,
if([Product ID] = peek([Product ID]),
if(len(Amount)=0,
peek(Amount),
peek(Amount)+Amount),
Amount)
) as Amount
INLINE [
Product ID, date, Amount
AA, 30-10-2012, 4
AA, 31-10-2012,
AA, 01-11-2012,
AA, 02-11-2012, 6
AA, 03-11-2012,
AA, 04-11-2012, -3
AA, 05-11-2012,
AA, 06-11-2012,
BB, 30-10-2012, 1
BB, 31-10-2012,
BB, 01-11-2012, 100
BB, 02-11-2012,
BB, 03-11-2012,
BB, 04-11-2012, -25
BB, 05-11-2012,
BB, 06-11-2012, 3
];
I think I got it with Toms solution:
IF(ProductID =Previous(ProductID) , RangeSum(Amount, Peek('Stock') ) , Amount) as Stock
What do you think?
I am goin to try this too.
Thanks Gysbert.