i dont have the whole picture but im thinking of somthing to be done within the load statment...
try doing an initial load and than use a resident load where you can sort your data by product id and date,
next you will be able to calculate a comulative stock and stock age using the previouse() function.
Date - Previouse(Date) as StockAge;
Resident StockTmp (in this table you initialy loaded your data)
Order by `ID`, `Date`;
Drop Table StockTmp;
this way you can hold a relevant stock level and stock age for each date and that level can be later selected by the end user...
Thanks for the reply.
Actually, we can not directly look at previous date.. if the previous date is also sales, then you may miss that point.
I seperate positive entries and sales in my script and then redistribute the positive values according to final stock level..
to find remaining stock, i use the below script.
load *, if(%Key_OnHand = peek('%Key_OnHand'),
if(peek('Remaining_Stock')>0,peek('Remaining_Stock')-PRIMARY_QUANTITY),OnHandQty-PRIMARY_QUANTITY) as Remaining_Stock
i would seperate it to several phases:
1. calculating stock level for stock entry points - for that i wouldnt have exclude the sales but multiplie them by (-1) to get the updated stock.
2. after we have the updated stock for all dates, in the resident query i would insert a 'where' in the resident to exclude the sales, only after we calculated the relevant stock level.
3. i would also use qualify to make sure this data doesnt mix with other data because its a summerized data.
this should be very simple.....
yes, phases that you are talking are very simple and straigth forward...
but in this case, you miss one point; when you create a summary view according to + and -
to find the final stock level, you could not say this 100 stock comes from which stock entry.
to do that you should create a loop for each item for each day.. since the data volume that i am dealing is so huge, i need to find some other way to handle this case.
i attach a simple QV document for your reference to make it clear for you.
In my sample data set, first 2 entries are + and they are 100, 25. after those entries there is a sales which is 25 and stock level drops to 100..
if i do your suggestion; my final stock level will seen as 100 and my first stock level will seen 100 and it says, it is related with that.. but according to FIFO (First In First Out), my 25 sales should be substract from my first inventory entry which 100..
I hope it will be much more clear for you now...
TEST_Stock.qvw 147.2 K