SeqNo

[upaliwije]

Hi

I have following data in my Data model

POLICY_NO	Period
1M00131A0000106	28-07-14
1M00131A0000082	23-01-14
1M00131A0000082	23-01-15
1M00131A0000120	16-10-14
1M00131A0000125	22-10-14
1M00131A0000125	22-10-15
1M00131A0000125	12-11-16
1M00131A0000091	04-04-14
1M00131A0000091	04-04-15
1M00111A000040	29-08-14
1M00131A0000081	17-01-14
1M00131A0000110	20-08-14
1M00131A0000110	20-08-15
1M00131A0000110	20-08-16
1M00131A0000110	20-08-17

I want add another column (seqNo) as shown below. That is when period of particular policy_no changes seqno should beincrsed and recorded

POLICY_NO	Period	Seqno
1M00131A0000106	28-07-14	1
1M00131A0000082	23-01-14	1
1M00131A0000082	23-01-15	2
1M00131A0000120	16-10-14	1
1M00131A0000125	22-10-14	1
1M00131A0000125	22-10-15	2
1M00131A0000125	12-11-16	3
1M00131A0000091	04-04-14	1
1M00131A0000091	04-04-15	2
1M00111A000040	29-08-14	1
1M00131A0000081	17-01-14	1
1M00131A0000110	20-08-14	1
1M00131A0000110	20-08-15	2
1M00131A0000110	20-08-16	3
1M00131A0000110	20-08-17	4

Pls help me how to write my script to obtain above result

[tresesco]

try like:

AutoNumber( RecNo(), POLICY_NO)  as  SeqNo

AutoNumber( RowNo(), POLICY_NO)  as  SeqNo

[Anil_Babu_Samineni]

May be this?

Load *,AutoNumber(RecNo(),POLICY_NO ) as seqno Inline [

POLICY_NO, Period

1M00131A0000106, 28-07-14

1M00131A0000082, 23-01-14

1M00131A0000082, 23-01-15

1M00131A0000120, 16-10-14

1M00131A0000125, 22-10-14

1M00131A0000125, 22-10-15

1M00131A0000125, 12-11-16

1M00131A0000091, 04-04-14

1M00131A0000091, 04-04-15

1M00111A000040, 29-08-14

1M00131A0000081, 17-01-14

1M00131A0000110, 20-08-14

1M00131A0000110, 20-08-15

1M00131A0000110, 20-08-16

1M00131A0000110, 20-08-17

];

[sunny_talwar · Accepted solution]

Another solution (and might give a slightly better performance then AutoNumber() function)

Table:

LOAD * INLINE [

    POLICY_NO, Period

    1M00131A0000106, 28-07-14

    1M00131A0000082, 23-01-14

    1M00131A0000082, 23-01-15

    1M00131A0000120, 16-10-14

    1M00131A0000125, 22-10-14

    1M00131A0000125, 22-10-15

    1M00131A0000125, 12-11-16

    1M00131A0000091, 04-04-14

    1M00131A0000091, 04-04-15

    1M00111A000040, 29-08-14

    1M00131A0000081, 17-01-14

    1M00131A0000110, 20-08-14

    1M00131A0000110, 20-08-15

    1M00131A0000110, 20-08-16

    1M00131A0000110, 20-08-17

];

FinalTable:

LOAD POLICY_NO,

  Period,

  If(POLICY_NO = Previous(POLICY_NO), RangeSum(Peek('Seqno'), 1), 1) as Seqno

Resident Table

Order By POLICY_NO, Period;

DROP Table Table;

[upaliwije]

When I apply this to my QVW like given below  it does not return any value, not even field name

PREMIUM:

Load *,AutoNumber(RecNo(),POLICY_NO ) as seqno

Resident sales;

[anushree1]

Hi Tresco,

Out of interest I want to know if there is any specific reason that you are recommending recno() in this case or could we use rowno() as well

[Anil_Babu_Samineni]

Can you send full script which you done over there? It should work with sample

[tresesco]

Hi,

We can use rowno() as well. In fact, using rowno() would be more meaningful in general. If you filter data in where clause and use recno() in this scenario, it would not give you the desired output. So, rowno() is safer in this scenario.

Thanks for drawing the attention.

[anushree1]

Cool Thanks

[sunny_talwar]

Correct me if I am wrong, but if there is a preceding load, we still might need RecNo() because then RowNo() isn't going to work.