Hi,
I am trying to find the best practice for presenting Inventory / Stock levels by day based on underlying transaction data that basically shows product, date, quanitity, where quantity can be positive (stock coming in) or negative (stock going out).
So far I have come up with three different solutions:
1. Some kind of IF-statement combined with a Date Island, e.g. =IF(TransDate<=Max(DateIslandDate))
2. Looping balances in script e.g.
FOR vDate = vStartDate to vEndDate
LOAD
vDate as BalanceDate,
ProdId,
Sum(Quantity) as Balance
RESIDENT TransTable
WHERE TransDate<=vDate
NEXT
3. Peek-solution similar to what HIC proposes:
Generating Missing Data In QlikView
With 10 000+ products all these solutions (except 1) take quite a long time to run and of course create a lot of rows if run over a couple of years 10 000 * 365 = 3.65 million per year. Alternative 3 gets quite complex if run over more dimensions than just Prod and Date. Alternative 2 is clean, but probably the slowest to run.
Anyone else has come up with better solutions?
Kind regards
Niklas
