Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loop Peek?

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

2 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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;

Help users find answers! Don't forget to mark a solution that worked for you!
MK_QSL
MVP
MVP

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;