seq_no

[upaliwije]

Hi,

I have following data(POLICY_NO AND PERIOD_FROM columns)  in my document I want to create a third column of data with SEQ_NO based on the YEAR(PERIOD_FROM )  for each POLICY_NO in data load script  as I have shown in my sample in red

POLICY_NO	PERIOD_FROM	SEQ_NO
NI00111C000039	10-Feb-2012	1
NI00111C000039	10-Feb-2013	2
NI00111C000039	10-Feb-2014	3
NI00121F000014	06-Mar-2012	1
NI00121F000014	06-Mar-2013	2
NI00121F000014	06-Mar-2014	3
NI00121F000014	06-Mar-2015	4
NI00144F0000098	27-Sep-2014	1
NI00144F0000098	05-Oct-2015	2
NI00121C000067	18-May-2012	1
NI00121C000067	18-May-2013	2
NI00121C000067	18-May-2014	3
NI00121C000067	18-May-2015	4

Pls help me with script

[sunny_talwar]

Try this:

Table:

LOAD * Inline [

POLICY_NO, PERIOD_FROM

NI00111C000039, 10-Feb-2012

NI00111C000039, 10-Feb-2013

NI00111C000039, 10-Feb-2014

NI00121F000014, 06-Mar-2012

NI00121F000014, 06-Mar-2013

NI00121F000014, 06-Mar-2014

NI00121F000014, 06-Mar-2015

NI00144F0000098, 27-Sep-2014

NI00144F0000098, 05-Oct-2015

NI00121C000067, 18-May-2012

NI00121C000067, 18-May-2013

NI00121C000067, 18-May-2014

NI00121C000067, 18-May-2015

];

FinalTable:

LOAD *,

  If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO

Resident Table

Order By POLICY_NO, PERIOD_FROM;

DROP Table Table;

[upaliwije]

Thanks Sunny

It is working with a bit of problem my output is as follows

C	YEAR	PERIOD_FROM	PERIOD_TO	NEW_REN	LAST_REN_YEAR	SEQ_NO
NI00111C000039	2012	10-Feb-2012	09-Feb-2013	REN	2014	3
NI00111C000039	2013	10-Feb-2013	09-Feb-2014	REN	2014	2
NI00111C000039	2014	10-Feb-2014	09-Feb-2015	REN	2014	1
NI00121F000014	2012	06-Mar-2012	05-Mar-2013	NEW	2015	4
NI00121F000014	2013	06-Mar-2013	05-Mar-2014	REN	2015	3
NI00121F000014	2014	06-Mar-2014	05-Mar-2015	REN	2015	2
NI00121F000014	2015	06-Mar-2015	05-Mar-2016	REN	2015	1
NI00144F0000098	2014	27-Sep-2014	26-Sep-2015	NEW	2015	2
NI00144F0000098	2015	05-Oct-2015	04-Oct-2016	REN	2015	1
NI00141H0000032	2014	10-Nov-2014	09-Nov-2015	NEW	2015	2
NI00141H0000032	2015	10-Nov-2015	09-Nov-2016	REN	2015	1

Seq No is in descending order.

My script is as follows

LOAD

BCOD,

CLA_CODE,

CLAIM_NO,

CUS_TYPE,

LOSS_DATE,

NEW_REN,

PAID_AMOUNT,

PERIOD_FROM,

PERIOD_TO,

POLICY_NO,

PRD_CODE,

PREMIUM,

REN,

YEAR,

If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR,

If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO

Resident FINAL_DATA

Order By POLICY_NO,PERIOD_FROM desc;

Kindly correct me

Pls also let me know how this rangesum function works

[sunny_talwar · Accepted solution]

You need ascending order? Try this:

LOAD

BCOD,

CLA_CODE,

CLAIM_NO,

CUS_TYPE,

LOSS_DATE,

NEW_REN,

PAID_AMOUNT,

PERIOD_FROM,

PERIOD_TO,

POLICY_NO,

PRD_CODE,

PREMIUM,

REN,

YEAR,

If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR,

If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO

Resident FINAL_DATA

Order By POLICY_NO, PERIOD_FROM asc;

[maxgro]

Table:

LOAD * Inline [

POLICY_NO, PERIOD_FROM

NI00111C000039, 10-Feb-2012

NI00111C000039, 10-Feb-2013

NI00111C000039, 10-Feb-2014

NI00121F000014, 06-Mar-2012

NI00121F000014, 06-Mar-2013

NI00121F000014, 06-Mar-2014

NI00121F000014, 06-Mar-2015

NI00144F0000098, 27-Sep-2014

NI00144F0000098, 05-Oct-2015

NI00121C000067, 18-May-2012

NI00121C000067, 18-May-2013

NI00121C000067, 18-May-2014

NI00121C000067, 18-May-2015

];

FinalTable:

LOAD

  *,

  AutoNumber(Right(PERIOD_FROM,4),POLICY_NO) as SEQ_NO

Resident Table

Order By POLICY_NO, PERIOD_FROM;

DROP Table Table;

[HirisH_V7]

Hi,

check this,

Temp:
LOAD * ,
AutoNumber(Num(Date(Date#(PERIOD_FROM,'DD-MMM-YYYY'),'DD/MM/YYYY')),POLICY_NO) as SEQ_NO,
Num(Date(Date#(PERIOD_FROM,'DD-MMM-YYYY'),'DD/MM/YYYY')) as DateNum
INLINE [
    POLICY_NO, PERIOD_FROM
    NI00111C000039, 10-Feb-2012
    NI00111C000039, 10-Feb-2013
    NI00111C000039, 10-Feb-2014
    NI00121F000014, 06-Mar-2012
    NI00121F000014, 06-Mar-2013
    NI00121F000014, 06-Mar-2014
    NI00121F000014, 06-Mar-2015
    NI00144F0000098, 27-Sep-2014
    NI00144F0000098, 05-Oct-2015
    NI00121C000067, 18-May-2012
    NI00121C000067, 18-May-2013
    NI00121C000067, 18-May-2014
    NI00121C000067, 18-May-2015
];

Table:


LOAD *
Resident Temp Order by POLICY_NO,SEQ_NO asc;

Output:

HTH,

PFA,

Hirish