Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
upaliwije
Contributor II

SeqNo

Hi

I have following data in my Data model

  

POLICY_NOPeriod
1M00131A000010628-07-14
1M00131A000008223-01-14
1M00131A000008223-01-15
1M00131A000012016-10-14
1M00131A000012522-10-14
1M00131A000012522-10-15
1M00131A000012512-11-16
1M00131A000009104-04-14
1M00131A000009104-04-15
1M00111A00004029-08-14
1M00131A000008117-01-14
1M00131A000011020-08-14
1M00131A000011020-08-15
1M00131A000011020-08-16
1M00131A000011020-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_NOPeriodSeqno
1M00131A000010628-07-141
1M00131A000008223-01-141
1M00131A000008223-01-152
1M00131A000012016-10-141
1M00131A000012522-10-141
1M00131A000012522-10-152
1M00131A000012512-11-163
1M00131A000009104-04-141
1M00131A000009104-04-152
1M00111A00004029-08-141
1M00131A000008117-01-141
1M00131A000011020-08-141
1M00131A000011020-08-152
1M00131A000011020-08-163
1M00131A000011020-08-174

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

1 Solution

Accepted Solutions

Re: SeqNo

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;

17 Replies
MVP
MVP

Re: SeqNo

try like:

AutoNumber( RecNo(), POLICY_NO)  as  SeqNo

AutoNumber( RowNo(), POLICY_NO)  as  SeqNo

Re: SeqNo

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

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: SeqNo

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
Contributor II

Re: SeqNo

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
Valued Contributor II

Re: SeqNo

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Re: SeqNo

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
Valued Contributor II

Re: SeqNo

Cool Thanks

Re: SeqNo

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.