MAX

upaliwije — Tue, 22 Mar 2016 10:42:27 GMT

I have following data in my DV document

My syntax is as follows

LOAD

POLICY_NO,

MAX(PREMIUM)AS PREMIUM,

Max(YEAR)+1 as YEAR,

Date(AddYears(Max(PERIOD_FROM),1),'DD-MMM-YYYY') as PERIOD_FROM,

Date(AddYears(Max(PERIOD_TO),1),'DD-MMM-YYYY') as PERIOD_TO,

'NOT RENEWED' as REM

RESIDENT F1

//WHERE MAX(YEAR)<2015

GROUP BY POLICY_NO

I want to get the premium in red to.35125/- that is the premium immediate past year Pls help to modify my syntax

Re: MAX

sunny_talwar — Tue, 22 Mar 2016 10:45:09 GMT

Try this:

LOAD

POLICY_NO,

FirstSortedValue(PREMIUM, -YEAR) AS PREMIUM,

Max(YEAR)+1 as YEAR,

Date(AddYears(Max(PERIOD_FROM),1),'DD-MMM-YYYY') as PERIOD_FROM,

Date(AddYears(Max(PERIOD_TO),1),'DD-MMM-YYYY') as PERIOD_TO,

'NOT RENEWED' as REM

RESIDENT F1

//WHERE MAX(YEAR)<2015

GROUP BY POLICY_NO;