6 Replies Latest reply: Jan 14, 2015 6:06 PM by Marco Wedel RSS

    Error with peek/previous

    Laura Sébille

      Hi everyone,

       

      I need to use peek or previous function so I can calculate the difference of dates between 2 rows.

       

      The table I load looks like this :

       

      XXX   timestamp

      A        42005,000045

      A        42007,000004

      A        42008,022222

      B        42004,999999

      B        42008,000343

       

       

      And I would like to obtain :

       

      XXX      interval

      A           (42007,000004-42005,000045)   --> I would like the result in minutes

      A           (42008,022222-42007,000004)

      B           (42008,000343-42004,999999)

       

      I also would like to get rid of the first line of each 'XXX' because I cannot calculate the interval.

       

      I was thinking of something like

       

      if(XXX = Peek(XXX), interval(timestamp-Peek(timestamp), 'm'))

       

      I first tried :

       

      LOAD  XXX,

                  timestamp,

                  Peek(timestamp) as test

      FROM ....

      order by XXX

       

      But I get the error "Scories after the instruction"... I don't know how to deal with that ? Is that because this field doesn't exist for the first row of each "XXX" ?

       

      What can I do ?

       

      Thanks for your help

       

      Have a good day.

      Laura

        • Re: Error with peek/previous
          Marcus Malinow

          Hi Laura,

           

          Try something like

           

          Table1:

          LOAD

          XXX,

          timestamp,

          if(NOT IsNull(Peek('XXX', -1),

              if(Peek('XXX', -1) = XXX,

                  peek('timestamp', -1))) as previoustimestamp

          FROM

          ....

          ORDER BY XXX, timestamp

           

          Table2:

          NOCONCATENATE

          LOAD

          XXX,

          timestamp,

          previoustimestamp,

          timestamp - previoustimestamp as timestampdiff

          RESIDENT Table1

          WHERE NOT IsNull(previoustimestamp);

           

          DROP TABLE Table1;

           

           

          Marcus

          • Re: Error with peek/previous
            Anand Chouhan

            Hi,

             

            Try this ways check for previous value and then add into the resident table and then in next resident subtract it with the timestamp field.

             

            Check load script

            T1:
            LOAD * INLINE [
                XXX, timestamp
                A, 42005.000045
                A, 42007.000004
                A, 42008.022222
                B, 42004.999999
                B, 42008.000343
            ];
            
            T2:
            LOAD 
            *,if(XXX = Previous(XXX), Peek(timestamp)) as [timestamp Previous]
            Resident T1 Order By XXX, timestamp;
            
            DROP Table T1;
            
            Final:
            LOAD *, Interval(timestamp - [timestamp Previous],'mm') as Diff
            Resident T2 Where IsNull([timestamp Previous]) <> -1;
            
            DROP Table T2;
            

             

             

            Regards

            Anand

            • Re: Error with peek/previous
              Marco Wedel

              Hi,

               

              one possible solution could be also:

               

              QlikCommunity_Thread_148410_Pic1.JPG

               

              tab1:
              LOAD XXX,
                  Timestamp(timestamp) as EndTimestamp
              INLINE [
                  XXX, timestamp
                  A, 42005.000045
                  A, 42007.000004
                  A, 42008.022222
                  B, 42004.999999
                  B, 42008.000343
              ]; 
              
              Right Join
              LOAD *,
                  Previous(EndTimestamp) as StartTimestamp,
                  Interval(EndTimestamp-Previous(EndTimestamp),'mm:ss') as [Interval (mm:ss)]
              Resident tab1
              Where XXX=Previous(XXX)
              Order By XXX, EndTimestamp;
              

               

              hope this helps

               

              regards

               

              Marco