Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;