remaining qty

[#1]

  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

[swuehl]

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

[#3]

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

Thanks

[swuehl]

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