Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

May be this:

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

sunny_talwar

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
Author

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?

sunny_talwar

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
Specialist III
Specialist III

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