2 Replies Latest reply: Jan 23, 2017 10:33 AM by Manish Kachhia RSS

    Loop Peek?

    Gary McDonald

      Hello All,

       

      I have some data that has multiple transactions per Policy Number.

       

      I have tried to use the following formula with the data set below but its not returning anything.

       

      If ([Policy Number] =Peek([Policy Number],1),rsh_effect_date) as NewEndDate

       

      What I intend to be returned is the rsh_effect_date of the next transaction where the policy number is the same.

       

      Please could someone help and show me where i'm going wrong?

       

      Policy NumberTransactionrsh_effect_date PolicyRenewalDateRequired Date
      PolNum1

      1

      16/02/201416/02/201516/02/2015
      PolNum1216/02/201516/02/201620/02/2015
      PolNum1320/02/201512/02/2016
      PolNum1412/02/201616/02/2016
      PolNum1516/02/201616/02/2017-
      PolNum6111/01/201403/03/2014
      PolNum62

      03/03/2014

      03/03/201503/03/2015
      PolNum63

      03/03/2015

      03/03/201625/09/2015
      PolNum6425/09/201510/11/2015
      PolNum6510/11/2015-

       

      Thanks

      Gary

        • Re: Loop Peek?
          Aurélien Martinez

          Hi,

           

          Load

            [Policy Number],

              Transaction,

              If([Policy Number] = Peek([Policy Number]), peek(rsh_effect_date),Null()) as [Required Date]

          Resident Data

          Order By [Policy Number], Transaction desc;

          • Re: Loop Peek?
            Manish Kachhia
            Data:
            LOAD [Policy Number], 
                 Transaction, 
                 rsh_effect_date, 
                 PolicyRenewalDate, 
                 [Required Date]
            FROM
            [https://community.qlik.com/thread/247196]
            (html, codepage is 1252, embedded labels, table is @1);
            
            
            Left Join (Data)
            Load 
              [Policy Number], 
                Transaction, 
                If([Policy Number] = Previous([Policy Number]),rsh_effect_date,Null()) as [Required Date]
            Resident Data;