Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Give each row a sequential number depending on several fields

Hi everyone,

I'm looking to number my rows, based on values of three fields.

The data looks like the following: I have UserId, VisitId, and HitNumber. However, HitNumber isn't always sequential, and I'd like to have another field that does give each row a sequential number, but only within the confines of UserId and VisitId.

I want to create the field PVNumber, that will give each UserId + VisitId + HitNumber combination a sequential number as shown below:

UserIdVisitIdHitNumberPVNumber
1234567987611
1234567987632
1234567987643
1234567987664
4567890765411
4567890765452
4567890765463

How can I do this?

Thanks in advance.

5 Replies

Re: Give each row a sequential number depending on several fields

May be this:

AutoNumber(RowNo(), UserId&VisitId) as PVNumber

Re: Give each row a sequential number depending on several fields

Script for your sample:

Table:

LOAD UserId,

    VisitId,

    HitNumber

FROM

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

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

FinalTable:

LOAD *,

  AutoNumber(RowNo(), UserId&VisitId) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table;


Capture.PNG

Not applicable

Re: Give each row a sequential number depending on several fields

Thanks...however, this is incredibly slow. I'm running the script on 5 million rows, and it's taken almost 17 hours so far to go through 1.8 million rows. Is there a way to speed this method up?

Re: Give each row a sequential number depending on several fields

If the data is already sorted, you can try it without the resident load. The reason for resident was to sort the data which might be slowing down for you

Table:

LOAD UserId,

    VisitId,

    HitNumber,

    AutoNumber(RowNo(), UserId&VisitId) as PVNumber

FROM

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

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

ramoncova06
Valued Contributor III

Re: Give each row a sequential number depending on several fields

one option is using peek

Table:

LOAD UserId,

    VisitId,

    HitNumber

FROM

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

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

FinalTable:

LOAD *,

  if(peek(UserId) = UserId and peek(VisitId) = VisitId, rangesum(peek(PVNumber),1),1) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table;




if you want to still use and autonumber like function then try using autonumberhash128 instead, I have seen it being faster


Table:

LOAD UserId,

    VisitId,

    HitNumber

FROM

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

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

FinalTable:

LOAD *,

  autonumberhash128(RowNo(), UserId,VisitId) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table

Community Browser