Insurance Policies

[upaliwije]

Hi Firends

I give below details(sample) of Insurance policies relating to four years with their policy periods. 1st five columns are available in the data source.

I want to to create a new field with the remark given there

Eg   Policy no NI00111C000039 commences in 2012 that id its 1st year and it is renewed in 2nd year as well but not rewed after the 3rd year

Eg Policy No NI00121F000014  is renewed for all 4 years

Eg Policy No NI00111D000019  lasts only for 2 years

POLICY_NO	YEAR	PERIOD_FROM	PERIOD_TO	NEW_REN	new_field
NI00111C000039	2012	10-Feb-2012	09-Feb-2013	REN	1ST YEAR
NI00111C000039	2013	10-Feb-2013	09-Feb-2014	REN	2ND YEAR
NI00111C000039	2014	10-Feb-2014	09-Feb-2015	REN	NOT RENEWED
NI00131A000028	2013	13-May-2013	12-May-2014	NEW	1ST YEAR
NI00131A000028	2014	13-May-2014	12-May-2015	REN	NOT RENEWED
NI00141A0000041	2014	06-Oct-2014	05-Oct-2015	NEW	1ST YEAR
NI00141A0000041	2015	06-Oct-2015	05-Oct-2016	REN	LAST YEAR
NI00121F000014	2012	06-Mar-2012	05-Mar-2013	NEW	1ST YEAR
NI00121F000014	2013	06-Mar-2013	05-Mar-2014	REN	2ND YEAR
NI00121F000014	2014	06-Mar-2014	05-Mar-2015	REN	3RD YEAR
NI00121F000014	2015	06-Mar-2015	05-Mar-2016	REN	LAST YEAR
NI00111D000019	2012	31-May-2012	30-May-2013	REN	1ST YEAR
NI00111D000019	2013	31-May-2013	30-May-2014	REN	NOT RENEWED
NI00141C0000106	2014	13-Oct-2014	12-Oct-2015	NEW	1ST YEAR
NI00141C0000106	2015	13-Oct-2015	12-Oct-2016	REN	LAST YEAR
NI00121D000053	2012	23-May-2012	22-May-2013	NEW	1ST YEAR
NI00121D000053	2013	23-May-2013	22-May-2014	REN	2ND YEAR
NI00141H0000032	2014	10-Nov-2014	09-Nov-2015	NEW	3RD YEAR
NI00141H0000032	2015	10-Nov-2015	09-Nov-2016	REN	LAST YEAR
NI00121C000069	2012	22-Jun-2012	21-Jun-2013	NEW	1ST YEAR
NI00121C000069	2013	22-Jun-2013	21-Jun-2014	REN	2ND YEAR
NI00121C000069	2014	22-Jun-2014	21-Jun-2015	REN	3RD YEAR
NI00121C000069	2015	22-Jun-2015	21-Jun-2016	REN	LAST YEAR

Pls help me to find solution for if possible with a sample QV file

[kaushiknsolanki]

Hi,

On what basis the "Last Year" is Flagged.

Regards,

Kaushik Solanki

[Digvijay_Singh]

Try using this sample -

T1:

Load * inline [

POLICY_NO YEAR PERIOD_FROM PERIOD_TO NEW_REN

NI00111C000039 2012 10-Feb-2012 09-Feb-2013 REN 

NI00111C000039 2013 10-Feb-2013 09-Feb-2014 REN 

NI00111C000039 2014 10-Feb-2014 09-Feb-2015 REN 

NI00131A000028 2013 13-May-2013 12-May-2014 NEW 

NI00131A000028 2014 13-May-2014 12-May-2015 REN 

NI00141A0000041 2014 06-Oct-2014 05-Oct-2015 NEW 

NI00141A0000041 2015 06-Oct-2015 05-Oct-2016 REN 

NI00121F000014 2012 06-Mar-2012 05-Mar-2013 NEW 

NI00121F000014 2013 06-Mar-2013 05-Mar-2014 REN 

NI00121F000014 2014 06-Mar-2014 05-Mar-2015 REN 

NI00121F000014 2015 06-Mar-2015 05-Mar-2016 REN 

NI00111D000019 2012 31-May-2012 30-May-2013 REN 

NI00111D000019 2013 31-May-2013 30-May-2014 REN 

NI00141C0000106 2014 13-Oct-2014 12-Oct-2015 NEW 

NI00141C0000106 2015 13-Oct-2015 12-Oct-2016 REN 

NI00121D000053 2012 23-May-2012 22-May-2013 NEW 

NI00121D000053 2013 23-May-2013 22-May-2014 REN 

NI00141H0000032 2014 10-Nov-2014 09-Nov-2015 NEW 

NI00141H0000032 2015 10-Nov-2015 09-Nov-2016 REN 

NI00121C000069 2012 22-Jun-2012 21-Jun-2013 NEW 

NI00121C000069 2013 22-Jun-2013 21-Jun-2014 REN 

NI00121C000069 2014 22-Jun-2014 21-Jun-2015 REN 

NI00121C000069 2015 22-Jun-2015 21-Jun-2016 REN ] (delimiter is spaces);

Left Join(T1)

Load POLICY_NO,

  Min(YEAR) as MinYear,

  Min(YEAR,2) as SYear,

  Min(YEAR,3) as TYear,

  Max(YEAR) as MaxYear

Resident T1

Group By POLICY_NO;

F1:

Load

  POLICY_NO,

  YEAR,

  PERIOD_FROM,

  PERIOD_TO,

  Pick(Match(YEAR,MinYear,SYear,TYear,MaxYear,Year(Today())-1),'1ST YEAR','2ND YEAR','3RD YEAR','LAST YEAR') as Remarks

Resident T1;

Drop table T1;

[upaliwije]

The data here relates to 4 years (2012-2015) if a policy is renewed in 2015 that is last year

[upaliwije]

Thanks

But my remark Not Renewed is not there please.

[Digvijay_Singh · Accepted solution]

Try this updated one, it will be there now -

Left Join(T1)

Load POLICY_NO,

  Min(YEAR) as MinYear,

  Min(YEAR,2) as SYear,

  Min(YEAR,3) as TYear,

  Max(YEAR) as MaxYear

Resident T1

Group By POLICY_NO;

F1:

Load

  POLICY_NO,

  YEAR,

  PERIOD_FROM,

  PERIOD_TO,

  Pick(Match(YEAR,MaxYear,MinYear,SYear,TYear),

  If(YEAR<Year(Today())-1,'NOT RENEWED','LAST YEAR'),'1ST YEAR','2ND YEAR','3RD YEAR') as Remarks

Resident T1;

Drop table T1;