MAX

[upaliwije]

HI

I have following data in my DV document

POLICY_NO	YEAR	PERIOD_FROM	PERIOD_TO	REM	PREMIUM
NI00081C000013	2012	02-Dec-2012	01-Dec-2013	1ST YEAR	42,168
NI00081C000013	2013	02-Dec-2013	01-Dec-2014	2ND YEAR	38,420
NI00081C000013	2014	02-Dec-2014	01-Dec-2015	3RD YEAR	36,373
NI00081C000013	2015	02-Dec-2015	01-Dec-2016	LAST YEAR	35,125
NI00081C000013	2016	02-Dec-2016	01-Dec-2017	NOT RENEWED	42,168

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

[sunny_talwar · Accepted solution]

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;