topic Re: Autonumber() doesn't start from 1 in QlikView

Autonumber() doesn't start from 1

peschu123 — Fri, 16 Aug 2013 14:08:40 GMT

Hi,

I try to build a datamodel with generic keys and I wanted to use Autonumber as an ID Field for a dimension table but it starts counting from 3.

I use the following code:

ACTION:

LOAD Distinct

Action,

Autonumber(Action) AS %ActionID

RESIDENT CLEANUP;

Another table even starts counting from 127.

Has it something to do with empty fields? I had a "WHERE len(Action)>0;" but the result was the same.

Someone who has an explanation?

Regards,

Peter

Re: Autonumber() doesn't start from 1

peschu123 — Fri, 16 Aug 2013 14:48:57 GMT

OK the help file and look at the tables clears it up ....

Re: Autonumber() doesn't start from 1

marcus_sommer — Fri, 16 Aug 2013 14:53:28 GMT

AFAIK autonumber() takes the source, a where and also an order by had therefore impact. Put a rowno() in the load and you can better see how it worked.

- Marcus

Re: Autonumber() doesn't start from 1

peschu123 — Fri, 16 Aug 2013 14:58:14 GMT

Hi Marcus,

thank you for your answer.

I tried rowno() first, but after that there are no distinct values..

I tried it with preceding load, but I always get all values instead of distinct values when I use rowno().

Re: Autonumber() doesn't start from 1

swuehl — Fri, 16 Aug 2013 15:03:45 GMT

I assume you already have your solution. If not, try autonumber with a second parameter for your bucket ID:

autonumber(Action, 'ActionTable') as %ActionID

Re: Autonumber() doesn't start from 1

robert99 — Fri, 02 May 2025 14:03:33 GMT

i had the same issue. There might be a bug in Qlik. Luckily it started at say three but had the right number from then on

The following script corrected the wrong starting number

I wanted a FSRNum for each Job_Num_Ref based on date of visit. From 1 then 2 3 etc if more than one visit

JobsFSRAutoREF: //temp dropped below
load
JOB_ID, //link. This is a automatic unique number for each row
[Jobs.Num_Ref] , // one Job_num will have one or more Job_IDs
AutoNumber( JOB_ID , Jobs.Num_Ref ) as FSRNumAuto
resident Jobs
Order BY Jobs.CREATED_AT
;

JobsFSRAutoRef2: //temp
JOIN (JobsFSRAutoREF)
LOAD
Jobs.Num_Ref, //link
min(FSRNumAuto) as FSRNumAutoMin
resident JobsFSRAutoREF
group by [Jobs.Num_Ref]
;

JobsFSRNumAuto:
LOAD
JOB_ID, //link
FSRNumAuto - FSRNumAutoMin +1 as FSRNumAutoAdj
resident JobsFSRAutoREF
;

DROP table JobsFSRAutoREF;

Re: Autonumber() doesn't start from 1

robert99 — Tue, 17 Jun 2025 04:13:35 GMT

Often a much better option

Solved: AutoNumber(RowNo(),[MyField]) takes too long - Qlik Community - 1815014

or this

Ranking within a group (quickly) in Qlik Load Script