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

[anushree1]

Hi Sunny,

I do not seem to understand why preceding load would not work.

I did try that and it looks all well to me.

Could you please refer the attachment and share your comments

[sunny_talwar]

You did not have preceding load and that is why both RowNo and RecNo worked, try this:

Temp:

LOAD *,

  0 as Dummy;

LOAD *,

AutoNumber(RowNo(),POLICY_NO) AS SeqNo1,

AutoNumber(RecNo(),POLICY_NO) AS SeqNo2;

LOAD POLICY_NO,

    Period

FROM

[https://community.qlik.com/thread/253566]

(html, codepage is 1252, embedded labels, table is @1);

Now RecNo() should still work, but AFAIK RowNo() won't work anymore....

[sunny_talwar]

Tested it here:

Table:

LOAD *,

  0 as Dummy;

LOAD *,

AutoNumber(RowNo(),POLICY_NO&'RowNo') AS SeqNo1,

AutoNumber(RecNo(),POLICY_NO&'RecNo') AS SeqNo2;

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

];

[tresesco]

Yes, it's another aspect of RowNo()/RecNo() usage consideration. However, it still holds that if the records get filtered in where clause, recno() would not give the expected output.

[Anil_Babu_Samineni]

Tested Done

[upaliwije]

Hi Sunny,

When I did as advised by you I get seqno 1,2 for the same policy period as shown below. Can you pls advise how to correct it

POLICY_NO	POL_PERIOD_TO	Seqno
1M00151A0000197	11-02-16	1
1M00151A0000197	11-02-16	2
1M00151A0000197	11-02-17	3
1M00151A0000197	11-02-18	4

Correct numbering should be (seqno)1,1,2,3

[sunny_talwar]

Try this:

Table:

LOAD * INLINE [

    POLICY_NO, POL_PERIOD_TO

    1M00151A0000197, 11-02-16

    1M00151A0000197, 11-02-16

    1M00151A0000197, 11-02-17

    1M00151A0000197, 11-02-18

];

FinalTable:

LOAD POLICY_NO,

  POL_PERIOD_TO,

If(POLICY_NO = Previous(POLICY_NO),

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

Resident Table

Order By POLICY_NO, POL_PERIOD_TO;

DROP Table Table;

[upaliwije]

Thanks . That is what I want exactly