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

[sunny_talwar]

From the looks, it doesn't seem that you need Max function. I would try to simplfy this:

If(Year(PERIOD_FROM) < 2015 and NEW_REN = 'REN', Year(AddYears(PERIOD_FROM, 1)), Year(PERIOD_TO)) as NEW_PERIOD

[upaliwije]

Thanks Sunny

But here I have more than one record for PERIOD_FROM   Eg 2012,2013,2014 so I want select Max year and that should be less than 2015. Can I use your script for that please

[jagan]

HI,

There is no need of Max() in the expression.

Regards,

Jagan.

[settu_periasamy]

You can try like..

Max({1}Year(PERIOD_FROM))

Edit: Ignore this. I didn't notice, it is a script side..

[sunny_talwar]

Not sure what exactly you are trying to do. May be a sample will help us understand better

[upaliwije]

This is output I require Sunny (NEW_PERIOD COLUMN)

POLICY_NO	PERIOD_FROM	PERIOD_TO	NEW_REN	NEW_PERIOD
NI00111A000013	07-Jul-2012	06-Jul-2013	REN
NI00111A000013	07-Jul-2013	06-Jul-2014	REN
NI00111A000013	07-Jul-2014	06-Jul-2015	REN
NI00111A000013	07-Jul-2015	06-Jul-2016	REN	2016
NI00121C000055	17-Feb-2012	16-Feb-2013	NEW
NI00121C000055	17-Feb-2013	16-Feb-2014	REN
NI00121C000055	17-Feb-2014	16-Feb-2015	REN
NI00121C000055	17-Feb-2015	16-Feb-2016	REN	2016
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
NI00111C000043	05-Jul-2012	04-Jul-2013	REN
NI00111C000043	05-Jul-2013	04-Jul-2014	REN
NI00111C000043	05-Jul-2014	04-Jul-2015	REN
NI00111C000043	05-Jul-2015	04-Jul-2016	REN	2016
NI00131D0000087	22-Nov-2012	21-Nov-2013	NEW
NI00131D0000087	22-Nov-2013	21-Nov-2014	REN	2015

[upaliwije]

This is not in expression side but in data load script, Is it possible to solve my problem using Peek() function

[upaliwije]

Pls have a look at my below script and advise.

FINAL_DATE:

load *,

if(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

//If(Year(PERIOD_FROM) < 2015 and NEW_REN = 'REN', Year(AddYears(PERIOD_FROM, 1)), Year(PERIOD_TO)) as NEW_PERIOD

Resident CLM_Data

ORDER BY POLICY_NO;

DROP TABLE CLM_Data;

[settu_periasamy]

Hi,

Like 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

];

NoConcatenate

FINAL_DATE:

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;

DROP TABLE CLM_Data;