5 Replies Latest reply: Jun 6, 2011 3:20 AM by el_tyrell RSS

    Expressions : how to get previous column value

      Hello,

       

      I just made a pivot table.

      Each line contains : an item number, a total stock quantity and then comes the "pivoted" data, a series of available stocks per date (or, to be more precise, per month).

       

      Example:

       

       

      ITEM     TOTAL STOCK     MONTH     0     1     2     3     4     5     6     7     8     9     10     11     12

      1          1000                    300               200   150               -10         -50         

      ...

       

       

      What I would like to do, with an expression, is the following :

       

      If (Month = 0) then stock at Month 0 position = Total Stock

      ==> I can do it with the following expression : if (ColumnNo()=1,QTESTOCKTOTAL, ...)

       

      But I'm stuck here :

       

      If (value is empty) then get previous value

       

      In the above example, I should get :

       

      ITEM     TOTAL STOCK     MONTH     0     1     2     3     4     5     6     7     8     9     10     11    12

      1          1000                    300   300   300   200   150   150   150   -10   -10   -50   -50    -50   -50

       

      Which expression can I use to do that ?

       

      Thanks !

       

      Stéphane

       

       

       


        • Re: Expressions : how to get previous column value

          Hi there, I would do it within the script, in order to keep the expressions simple, like this:

           

          Table:

          crossTable('Month','Amount',2)

          Load * inline

          [

          ITEM,     TOTAL STOCK,   0,     1,     2,     3,     4,     5,     6,     7,     8,     9,     10,     11,    12

          1,          1000,                    300,     ,      ,   200,   150,      ,      ,   -10,      ,   -50,       ,       ,     

          ]

          ;

           

          Table2:

          Load ITEM, Month, if(ITEM = previous(ITEM) and not len(Amount), peek(InventoryAmount), Amount) as InventoryAmount

          resident Table

          order by ITEM, Month;

           

          drop table Table;

           

           

          Hence, the expression should be somethin like this:

           

          sum(InvetoryAmount)

           

          Regards.