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

Give each row a sequential number depending on several fields

Tue, 31 May 2016 23:17:26 GMT

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.

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

sunny_talwar — Wed, 01 Jun 2016 00:16:57 GMT

May be this:

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

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

sunny_talwar — Wed, 01 Jun 2016 02:43:15 GMT

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;

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

Thu, 02 Jun 2016 13:43:30 GMT

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

sunny_talwar — Thu, 02 Jun 2016 13:56:05 GMT

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);

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

ramoncova06 — Thu, 02 Jun 2016 21:43:12 GMT

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