Hello!
I had posted previously, and John supplied me with some code that really helped, but I believe I need some further assistance…
Scenario:
I am trying to calculate the number of week's stock I have for each product based on the stock available against the forecast.
The forecast is extracted from a 52 week array, in a field called Qty. Within a table called Inventory, I have fields for Available Stock, Required, On Hand and On Order. These need to be calculated into my available stock.
The forecast is in calendar months, years 2008 - 2010 in 52 weeks. I need QlikView to work out what current week we are in, as I need it to count down from there. I then need QlikView to countdown my stock levels, updating the amount for each week until 0, showing what week it will run out.
The inventory table is a standard figure which is updated real time (no time period).
The code John originally supplied was:
[Code]
[Inventory]:
LOAD * INLINE [
Stock Item, Week, Forecast Amount
1, 1, 20
1, 2, 10
1, 3, 30
1, 4, 60
1, 5, 10
2, 1, 1
2, 2, 2
2, 3, 3
2, 4, 5
2, 5, 8
2, 6, 13
2, 7, 21
2, 8, 34
2, 9, 55
2,10, 89
3, 1, 10
3, 2, 10
3, 3, 10
3, 4, 10
3, 5, 10
3, 6, 10
3, 7, 10
3, 8, 10
3, 9, 10
3,10, 10
3,11, 10]
;
LEFT JOIN
LOAD
"Stock Item"
,"Week"
,rangemax(0,if("Week"=1,ceil(rand()*100),peek("Available")-previous("Forecast Amount"))) as "Available"
RESIDENT [Inventory]
;
[Weeks of Stock]:
LOAD
"Stock Item"
,min("Week") - 1 as "Weeks Available"
RESIDENT [Inventory]
WHERE "Available" <= 0
GROUP BY "Stock Item"
;
Which I integrated to make:
[Code]
Inventory:
LOAD "in_domain",
"in_part" as Part,
"in_qty_all" as Quantity,
"in_qty_avail" as AvailableStock,
"in_qty_chg" as Changed,
"in_qty_oh" As [On Hand],
"in_qty_ord" as Ordered,
"in_qty_req" as Required,
"in_site";
SQL SELECT "in_domain",
"in_part",
"in_qty_all",
"in_qty_avail",
"in_qty_chg",
"in_qty_oh",
"in_qty_ord",
"in_qty_req",
"in_site"
FROM PUB."in_mstr";
TempForecast:
LOAD fcs_fcst_qty as Qty,
"fcs_year" as Year,
"fcs_part" as Part;
SQL SELECT
"fcs_fcst_qty",
"fcs_part",
"fcs_year"
FROM PUB."fcs_sum";
Join (Inventory)
Load Part,
Year,
Year&'.'&num(iterno(),'00') as YearWeek,
Subfield(Qty,';',Iterno()) as Qty,
Iterno() as Week
Resident TempForecast
WHILE iterno()<substringcount(Qty,';')+2;
Drop Table TempForecast;
LEFT JOIN
LOAD
Part
,"Week"
,rangemax(0,if("Week"=1,ceil(rand()*100),peek("Available")-previous("Qty"))) as "Available"
RESIDENT [Inventory]
;
[Weeks of Stock]:
LOAD
Part
,min("Week") - 1 as "Weeks Available"
RESIDENT [Inventory]
WHERE "Available" <= 0
GROUP BY Part
;
You're help will be muchly appreciated!!!
Kind Regards,
Dayna Litherland
