topic Re: Loop Peek? in QlikView

Loop Peek?

Mon, 23 Jan 2017 15:23:55 GMT

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 Number	Transaction	rsh_effect_date	PolicyRenewalDate	Required Date
PolNum1	1	16/02/2014	16/02/2015	16/02/2015
PolNum1	2	16/02/2015	16/02/2016	20/02/2015
PolNum1	3	20/02/2015		12/02/2016
PolNum1	4	12/02/2016		16/02/2016
PolNum1	5	16/02/2016	16/02/2017	-
PolNum6	1	11/01/2014		03/03/2014
PolNum6	2	03/03/2014	03/03/2015	03/03/2015
PolNum6	3	03/03/2015	03/03/2016	25/09/2015
PolNum6	4	25/09/2015		10/11/2015
PolNum6	5	10/11/2015		-

Thanks

Gary

Re: Loop Peek?

Aurelien_Martinez — Mon, 23 Jan 2017 15:28:38 GMT

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?

MK_QSL — Mon, 23 Jan 2017 15:33:33 GMT

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;