topic Re: SeqNo in QlikView

SeqNo

upaliwije — Thu, 16 Mar 2017 10:36:49 GMT

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

Re: SeqNo

tresB — Thu, 16 Mar 2017 10:43:12 GMT

try like:

AutoNumber( RecNo(), POLICY_NO) as SeqNo

AutoNumber( RowNo(), POLICY_NO) as SeqNo

Re: SeqNo

Anil_Babu_Samineni — Thu, 16 Mar 2017 10:45:19 GMT

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

];

Re: SeqNo

sunny_talwar — Thu, 16 Mar 2017 11:05:57 GMT

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;

Re: SeqNo

upaliwije — Thu, 16 Mar 2017 11:14:10 GMT

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;

Re: SeqNo

anushree1 — Thu, 16 Mar 2017 11:14:27 GMT

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

Re: SeqNo

Anil_Babu_Samineni — Thu, 16 Mar 2017 12:38:17 GMT

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

Re: SeqNo

tresB — Thu, 16 Mar 2017 12:47:36 GMT

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.

Re: SeqNo

anushree1 — Thu, 16 Mar 2017 12:54:09 GMT

Cool Thanks

Re: SeqNo

sunny_talwar — Thu, 16 Mar 2017 13:01:51 GMT

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.

Re: SeqNo

anushree1 — Thu, 16 Mar 2017 13:09:45 GMT

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

Re: SeqNo

sunny_talwar — Thu, 16 Mar 2017 13:13:23 GMT

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....

Re: SeqNo

sunny_talwar — Thu, 16 Mar 2017 13:16:44 GMT

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

];

Re: SeqNo

tresB — Thu, 16 Mar 2017 13:24:48 GMT

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.

Re: SeqNo

Anil_Babu_Samineni — Thu, 16 Mar 2017 15:04:32 GMT

Tested Done

Re: SeqNo

upaliwije — Thu, 16 Mar 2017 18:07:47 GMT

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

Re: SeqNo

sunny_talwar — Thu, 16 Mar 2017 18:14:18 GMT

Try this:

Table:

LOAD * INLINE [

POLICY_NO, POL_PERIOD_TO

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;

Re: SeqNo

upaliwije — Fri, 17 Mar 2017 02:40:37 GMT

Thanks . That is what I want exactly