Max Period

[upaliwije]

Hi

IF (MAX(YEAR(PERIOD_FROM))<2015 AND  NEW_REN='REN',YEAR(PERIOD_FROM+365),YEAR(PERIOD_TO))AS NEW_PERIOD

My above syntax works well without MAX condition. Have I made any error in adding MAX to above script. if so pls correct me

[upaliwije]

Thanks !

but my issue is NEW_PERIOD should be returned only for the last record of the policy as given below

NI00121C000070	25-Jun-2012	24-Jun-2013	NEW
NI00121C000070	25-Jun-2013	24-Jun-2014	REN	2014
NI00111C000040	18-Apr-2012	17-Apr-2013	REN
NI00111C000040	18-Apr-2013	17-Apr-2014	REN
NI00111C000040	18-Apr-2014	17-Apr-2015	REN	2015

[jagan]

FINAL_DATE:

load *,

if(IF RowNo() = 1 OR Peek( 'POLICY_NO')<> POLICY_NO,NEW_PERIOD)AS NEW_PERIOD

;

LOAD *,

IF (YEAR(PERIOD_FROM)<2015 AND  NEW_REN='REN',YEAR(PERIOD_FROM+365),YEAR(PERIOD_TO))AS NEW_PERIOD

Resident CLM_Data

ORDER BY POLICY_NO, PERIOD_TO DESC;

DROP TABLE CLM_Data;

Regards,

Jagan.

[upaliwije]

Then I get the following error msg

Error in expression:

')' expected

FINAL_DATE:

load *,

if(IF RowNo() = 1 OR Peek('POLICY_NO')<> POLICY_NO,NEW_PERIOD)AS NEW_PERIOD

[settu_periasamy · Accepted solution]

Hi,

Try this..

CLM_Data:
LOAD * INLINE [
POLICY_NO, PERIOD_FROM, PERIOD_TO, NEW_REN
NI00111A000013, 7/7/2012, 7/6/2013, REN
NI00111A000013, 7/7/2013, 7/6/2014, REN
NI00111A000013, 7/7/2014, 7/6/2015, REN
NI00111A000013, 7/7/2015, 7/6/2016, REN
NI00121C000055, 2/17/2012, 2/16/2013, NEW
NI00121C000055, 2/17/2013, 2/16/2014, REN
NI00121C000055, 2/17/2014, 2/16/2015, REN
NI00121C000055, 2/17/2015, 2/16/2016, REN
NI00121C000070, 6/25/2012, 6/24/2013, NEW
NI00121C000070, 6/25/2013, 6/24/2014, REN
NI00111C000040, 4/18/2012, 4/17/2013, REN
NI00111C000040, 4/18/2013, 4/17/2014, REN
NI00111C000040, 4/18/2014, 4/17/2015, REN
NI00111C000043, 7/5/2012, 7/4/2013, REN
NI00111C000043, 7/5/2013, 7/4/2014, REN
NI00111C000043, 7/5/2014, 7/4/2015, REN
NI00111C000043, 7/5/2015, 7/4/2016, REN
NI00131D0000087, 11/22/2012, 11/21/2013, NEW
NI00131D0000087, 11/22/2013, 11/21/2014, REN
];


FINAL_DATE:
LOAD *,
if(Peek(POLICY_NO)<> POLICY_NO,
IF (YEAR(PERIOD_FROM)<2015 AND  NEW_REN='REN',YEAR(PERIOD_FROM+365),YEAR(PERIOD_TO)))AS NEW_PERIOD
Resident CLM_Data
ORDER BY POLICY_NO, PERIOD_TO DESC;
DROP TABLE CLM_Data;

[jagan]

Hi,

Try this

FINAL_DATE:

load *,

if(RowNo() = 1 OR Peek( 'POLICY_NO')<> POLICY_NO, NEW_PERIOD)AS NEW_PERIOD_New

;

LOAD *,

IF (YEAR(PERIOD_FROM)<2015 AND  NEW_REN='REN',YEAR(PERIOD_FROM+365),YEAR(PERIOD_TO))AS NEW_PERIOD

Resident CLM_Data

ORDER BY POLICY_NO, PERIOD_TO DESC;

DROP TABLE CLM_Data;

Regards,

jagan.

[upaliwije]

Thanks Settu,

Your answer is what is really required by me. But I have one problem. When I apply your script to my document it works except for policy No

NI00111C000040

Pls have a look at my QV document and advise me  what is wrong

[settu_periasamy]

Which year you are getting?, Mine is showing like this when i use the above script.

[upaliwije]

Thanks Settu,

Your answer is fine . I have made a mistake in my data

Thanks a lot for your help