Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi at All,
I need to create a remaing qty and stock report; In the script I don't want to use
the FOR loop becouse it generates many many rows and the memory of server crashes.
Please, someone have a smart solution for this case? Can I have a support script for
this for this example below?
Many Rhanks
date of reload from function today() is 09/07/2011
onventory:
product remaining qty posting date
AAA 10 01/01/2011
BBB 20 05/01/2011
AAA 20 07/01/2011
CCC 60 07/01/2011
BBB 10 08/01/2011
AAA 90 09/01/2011
in qlikview I would to create this grid (ALL in one table) :
report of product AAA:
posting product remaining
01/01/2011 AAA 10
02/01/2011 AAA 10
03/01/2011 AAA 10
04/01/2011 AAA 10
05/01/2011 AAA 10
06/01/2011 AAA 10
07/01/2011 AAA 20
08/01/2011 AAA 20
09/01/2011 AAA 60
report of product BBB:
posting product remaining
01/01/2011 BBB 0
02/01/2011 BBB 0
03/01/2011 BBB 0
04/01/2011 BBB 0
05/01/2011 BBB 20
06/01/2011 BBB 20
07/01/2011 BBB 20
08/01/2011 BBB 10
09/01/2011 BBB 10
report of product CC:
posting product remaining
01/01/2011 CCC 0
02/01/2011 CCC 0
03/01/2011 CCC 0
04/01/2011 CCC 0
05/01/2011 CCC 60
06/01/2011 CCC 60
07/01/2011 CCC 60
08/01/2011 CCC 60
09/01/2011 CCC 60
Report of product AAA for the last day should be 90, not 60, right?
I think you don't need to handle the remaining qty in the script, except adding a master calendar (to be able to create the posting column).
You can then use the master calendar as a dimension to your tables (second dimension would be product, maybe set to a fixed value per table, if you want, or all in one table).
Then you just need to look in each row for the latest posting date and its remaining qty.
I wouldn't necessarily link the master calendar to the posting dates, so you could use simple if clauses and First SortedValue() function to get the values you need.
Regards,
Stefan
Yes, 90 , Please could to send the qvw script for the example I wrote?
Thanks
Please have a look at attached example.
I thought a little bit and changed my mind:
I added a field in the data table with the end date for the period a remaining qty is valid (i.e. the counter part to the posting date). This allows to retrieve the remaining qty just by checking the start and end date (comparison by master calendar date).
The script now looks like:
MasterCalendar:
LOAD
Date(Makedate(2011,1,1)+RecNo()-1) as Date
// add more date functions and fields here, like year, month, weekday etc.
autogenerate 15;
tmpInventory:
LOAD * INLINE [
product, remaining qty, posting date
AAA, 10,01/01/2011
BBB, 20,05/01/2011
AAA, 20,07/01/2011
CCC, 60,07/01/2011
BBB, 10,08/01/2011
AAA, 90,09/01/2011
];
Inventory:
Load product,
[remaining qty],
[posting date],
Date(if(isnull(Previous([posting date])) or previous(product) <> product,today(),Previous([posting date]-1))) As [date effective To]
Resident tmpInventory
Order by product, [posting date] Desc;
Drop table tmpInventory;
and I used a straight table with dimensions product and date and expressions
=sum(if(Date <= [date effective To] and Date >= [posting date], [remaining qty],0))
resp.
=Date(max(if([posting date]<= Date, [posting date])))
to get the current remaining qty per date and the last posting date per date.
Hope this helps,
Stefan