topic Re: Max Period in QlikView

Max Period

upaliwije — Fri, 04 Mar 2016 02:57:18 GMT

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

Re: Max Period

sunny_talwar — Fri, 04 Mar 2016 03:00:56 GMT

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

Re: Max Period

upaliwije — Fri, 04 Mar 2016 03:08:33 GMT

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

Re: Max Period

jagan — Fri, 04 Mar 2016 03:09:25 GMT

HI,

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

Regards,

Jagan.

Re: Max Period

settu_periasamy — Fri, 04 Mar 2016 03:13:20 GMT

You can try like..

Max({1}Year(PERIOD_FROM))

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

Re: Max Period

sunny_talwar — Fri, 04 Mar 2016 03:20:10 GMT

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

Re: Max Period

upaliwije — Fri, 04 Mar 2016 03:39:14 GMT

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

Re: Max Period

upaliwije — Fri, 04 Mar 2016 04:13:12 GMT

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

Re: Max Period

upaliwije — Fri, 04 Mar 2016 04:24:51 GMT

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;

Re: Max Period

settu_periasamy — Fri, 04 Mar 2016 04:35:07 GMT

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;

Re: Max Period

upaliwije — Fri, 04 Mar 2016 04:47:25 GMT

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

Re: Max Period

jagan — Fri, 04 Mar 2016 05:03:50 GMT

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.

Re: Max Period

upaliwije — Fri, 04 Mar 2016 05:09:39 GMT

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

Re: Max Period

settu_periasamy — Fri, 04 Mar 2016 05:11:49 GMT

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;

Re: Max Period

jagan — Fri, 04 Mar 2016 05:22:42 GMT

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.

Re: Max Period

upaliwije — Fri, 04 Mar 2016 05:40:41 GMT

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

Re: Max Period

settu_periasamy — Fri, 04 Mar 2016 05:50:10 GMT

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

Re: Max Period

upaliwije — Fri, 04 Mar 2016 05:53:53 GMT

Thanks Settu,

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

Thanks a lot for your help