2 Replies Latest reply: Jun 24, 2013 11:20 AM by Ralph Graham RSS

    Grouping Data (with peek)

      Hello,

       

      I am trying to group data by week but am having a problem with one aspect of it (see below). For the waste and production data it is a simple matter of summing and then grouping by week but with stock the final figure of the week (in the below example it is 2 000 for week 23) is the figure I want to capture so that it is closing stock, however I don't want the other stock figures to be summed. And then the closing stock figure for week 23 would be the opening stock figure for week 24. I'm thinking peek is the solution however I'm not having any success with using it in a grouping scenario.

       

      Thanks,


      Ralph 

       

      Raw Table

       

      DateWeekStockWasteProduction
      11/06/13

      23

      1000

      1500100 000

      12/06/13

      2320002500200 000
      13/06/132310001000200 000
      14/06/13233000500400 000
      15/06/132320002000500 000
      16/06/132340004000200 000
      17/06/132320005000300 000
      18/06/132430002000150 000
      19/06/13241000500250 000
      20/06/13

      24

      4000100050 000
      21/06/132450002500500 000
      22/06/13242000500150 000
      23/06/132420001500250 000
      24/06/13243000500020 000

       

      Expected Table

       

      WeekStockWasteProduction
      232 00016 5001 900 000
      243 00013 0001 370 000
        • Re: Grouping Data (with peek)

          This script appears to work:

           

          RAW_Data:

          LOAD * INLINE [
          Date,Week,Stock,Waste,Production
          11/06/13,23,1000,1500,100000
          12/06/13,23,2000,2500,200000
          13/06/13,23,1000,1000,200000
          14/06/13,23,3000,500,400000
          15/06/13,23,2000,2000,500000
          16/06/13,23,4000,4000,200000
          17/06/13,23,2000,5000,300000
          18/06/13,24,3000,2000,150000
          19/06/13,24,1000,500,250000
          20/06/13,24,4000,1000,50000
          21/06/13,24,5000,2500,500000
          22/06/13,24,2000,500,150000
          23/06/13,24,2000,1500,250000
          24/06/13,24,3000,5000,20000]
          ;



          NewData:
          LOAD Date,Week,Stock,Waste,Production,IF(PEEK('Week',RowNo(),'RAW_Data')=Week,NULL(),Stock) AS ClosingStock RESIDENT RAW_Data ORDER BY Date;

          DROP TABLE RAW_Data;

          FinalTable:
          LOAD Week,Sum(ClosingStock) AS Stock, Sum(Waste) AS Waste, Sum(Production) AS Production RESIDENT NewData GROUP BY Week;

          DROP TABLE NewData;