Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
UserId | VisitId | HitNumber | PVNumber |
---|---|---|---|
1234567 | 9876 | 1 | 1 |
1234567 | 9876 | 3 | 2 |
1234567 | 9876 | 4 | 3 |
1234567 | 9876 | 6 | 4 |
4567890 | 7654 | 1 | 1 |
4567890 | 7654 | 5 | 2 |
4567890 | 7654 | 6 | 3 |
How can I do this?
Thanks in advance.
May be this:
AutoNumber(RowNo(), UserId&VisitId) as PVNumber
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;
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?
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);
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