2 Replies Latest reply: Jul 13, 2017 4:49 AM by Prateek Arora RSS

    Peek()

    Prateek Arora

      Hello All,

       

      I have the below data file. I have to find out the difference in the stock from the last reading of the previous date to the first reading of the next day.

       

      DateProduct CodeTimeStock
      28-01-2015160012:00:00 1324
      28-01-2015170012:00:001745
      28-01-2015180012:00:000
      28-01-2015160018:00:001424
      28-01-2015170018:00:001845
      28-01-2015180018:00:0010
      28-01-2015160023:30:001624
      28-01-2015170023:30:001924
      28-01-2015180023:30:0030
      29-01-2015160001:00:001614
      29-01-2015170001:00:001915
      29-01-2015180001:00:0030
      29-01-2015160023:59:592451
      29-01-2015170023:59:592784
      29-01-2015180023:59:5956
      30-01-2015160012:30:002461
      30-01-2015170012:30:002784
      30-01-2015180012:30:0055

       

       

      So my resultant table should look something like:

       

      DateProduct CodeDifference
      28-01-20151600-
      28-01-20151700-
      28-01-20151800-
      29-01-2015160010 (1624-1614)
      29-01-201517009 (1924-1915)
      29-01-201518000 (30-30)
      30-01-20151600-10 (2451-2461)
      30-01-201517000 (2784-2784)
      30-01-201518001 (56-55)

       

      I have tried using the peek function but couldn't combine it to the max,min(Time) of the day to get the difference

       

      Regards

        • Re: Peek()
          liron baram

          this script should do the trick

          DataTemp:

          LOAD

              "Date",

              "Product Code",

              "Time",

              Stock

          FROM [lib://Comm/Book1.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

           

           

           

           

          Data:

          load *,

              if(Previous("Product Code")="Product Code" and "Date" <> Previous("Date"), Previous(Stock)-Stock) As StockDelta

          Resident DataTemp

          Order By "Product Code","Date","Time";

           

           

          drop Table DataTemp;

           

          also attach is a demo app