topic remaining qty in QlikView

remaining qty

Thu, 25 Aug 2011 08:11:45 GMT

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

remaining qty

swuehl — Thu, 25 Aug 2011 08:54:54 GMT

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

remaining qty

Thu, 25 Aug 2011 11:55:52 GMT

Yes, 90 , Please could to send the qvw script for the example I wrote?

Thanks

Re: remaining qty

swuehl — Thu, 25 Aug 2011 17:16:31 GMT

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