Autonumber() doesn't start from 1

[peschu123]

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

[peschu123 · Accepted solution]

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

[marcus_sommer]

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

[peschu123]

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().

[swuehl]

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

autonumber(Action, 'ActionTable') as %ActionID

[robert99]

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;

[robert99]

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