2 Replies Latest reply: Oct 8, 2013 6:44 AM by Janne Vauhkonen RSS

    Peek function

      Hi

       

      I am strugling with little bit with peek function. I have inventories in data table. In this table you can see inventory per period if there has been material movements within the period. If there is no value in this table for some period, it means that the inventory value is same as previous period, so in case there is no movements within in period I need to use peek function to return the last value for the period.

       

      I have now following script

       

       

      Load

      AccumulationKey,
      if(len(trim(MonthEndValue))=0,peek(MonthEndValue),MonthEndValue) as StockValue,
      if(len(trim(MonthEndValue))=0,peek(MonthEndVolume),MonthEndVolume) as StockVolume,
      Year,
      Month,
      Warehouse,
      Product

       

      Resident ValAccum;

       

      This script doesn't work too properly. As you can see below, in case there is now inventory value in some cases it returns the last value from the last series In Example below it returns the last value from the "group" 4444|11111 to 4444|22222 cause there isn't any data for 4444|22222 before Feb-2013.

       

      How should I change the formula to say QlikvIew to stop searching the last value when the group change (AccumulationKey)?

       

      AccumulationKeyStock valueStock volumeYearMonthWarehouseProduct
      4444|11111589.91002013Sep444411111
      4444|11111589.91002013Oct444411111
      4444|22222589.91002012Feb444422222
      4444|22222589.91002012Mar444422222
      4444|22222589.91002012Apr444422222
      4444|22222589.91002012May444422222
      4444|22222589.91002012Jun444422222
      4444|22222589.91002012Jul444422222
      4444|222220.002012Aug444422222
      4444|222220.002012Sep444422222
      4444|222220.002012Oct444422222
      4444|222220.002012Nov444422222
      4444|222220.002012Dec444422222
      4444|222220.002013Jan444422222
      4444|222223837.66002013Feb444422222

       

       

      Many thanks already in advance!

       

      Regards

      Janne