2 Replies Latest reply: Dec 27, 2017 8:47 PM by Pablo Labbe RSS

    Peek Value | Carry max value of previously read records

    Byron Van Wyk

      Hi All,

       

      I have the following table

       

      ID,Owner,Start,End,ExpectedResult,
      1,A,2,8,8,
      2,A,4,6,8,
      3,A,5,7,8,
      4,A,9,12,12,
      5,B,3,4,4,
      6,B,5,13,13,
      7,B,8,10,13,
      8,B,11,12,13,
      9,B,15,17,17,
      10,B,16,18,18

       

      I cannot correctly peek the expected result. The following conditions are in play

       

      1. Table is grouped by Owner and sorted ASC Start

      2. If Owner = Previous(Owner) then check if the current start row is GREATER than ANY PREVIOUSLY read end row for that OWNER

      if it is, then use that rows end date otherwise use the previously read row.

       

      My ExpectedResult shows you what I am after. How would I write this in script

        • Re: Peek Value | Carry max value of previously read records
          Sunny Talwar

          Try this

           

          Table:

          LOAD * INLINE [

              ID, Owner, Start, End

              1, A, 2, 8

              2, A, 4, 6

              3, A, 5, 7

              4, A, 9, 12

              5, B, 3, 4

              6, B, 5, 13

              7, B, 8, 10

              8, B, 11, 12

              9, B, 15, 17

              10, B, 16, 18

          ];


          FinalTable:

          LOAD *,

          If(Owner = Previous(Owner), RangeMax(Peek('ExpectedResult'), End), End) as ExpectedResult

          Resident Table

          Order By Owner, ID;


          DROP Table Table;

          • Re: Peek Value | Carry max value of previously read records
            Pablo Labbe

            When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.