topic Re: Max number for autonumber in App Development

Max number for autonumber

nerakeglevic — Fri, 15 Nov 2024 23:26:46 GMT

Hi all,

I have a problem with autonumber function.

So, the problem is that for 2 different original keys we have one autonumber value so the keys in our app are all messed up.

Important to say is that app has 350 mil rows.

Here is the example:

So, field HASH_SHIFT_RECEIPT_ID is autonumber field, originaly was made as SHIFT_ID&'-'& RECAIPT_ID.

Any help is appreciated.

I also tried with autonumberhash128 and 256, same thing happens.

Br,

Nera

Re: Max number for autonumber

rubenmarin — Sat, 04 Dec 2021 11:22:35 GMT

Hi, if the sentence is "Autonumber(SHIFT_ID&'-'& RECAIPT_ID) as HASH_SHIFT_RECEIPT_ID" it should give different autonuber to each different SHIFT_ID, maybe some steps later in script is relating data in an unexpected way.

You can try to test only this portion of code to check values assigned, just add an "exit script;" after creating the autonumber to check it's values.

Re: Max number for autonumber

marcus_sommer — Sat, 04 Dec 2021 11:35:58 GMT

Within the most cases has an autonumberhash() no benefits - so you should consider to change it to a normal autonumber.

Autonumber() has a second parameter to differentiate multiple autonumber-fields. By applying autonumber as a script-statement it differentiate them automatically. The last measure is further more performant and won't touch any optimized load-approaches.

Beside this the number of rows has often only a inferior meaning. More important is the distinct number of field-values. In this regard you may rethink if an autonumber - probably as a key between fact-tables and/or even link-tables - is sensible at all. By using a star-scheme it's mostly not needed.

- Marcus

Re: Max number for autonumber

nerakeglevic — Sat, 04 Dec 2021 13:12:57 GMT

Hi,

thank you both for answers.

To be more precise, I created HASH_SHIFT_RECEIPT_ID like this

LOAD

SHIFT_ID &'-'& RECEIPT_ID AS HASH_SHIFT_RECEIPT_ID

RESIDENT table;

After that I did all needed calculations and at the end of script, I have added this:

AUTONUMBER HASH_SHIFT_RECEIPT_ID;

I wanted to make sure that I do autonumber after all calculations were done.

Do you really think that it is a problem if a number of distinct values is greater than max possible number for autonumber function?

I really have a lot of data and I think that it is possible. Also, I wanted to add autonumber because of perfomances in frontend.

Thank you really for all help.

Br,

Nera

Re: Max number for autonumber

rubenmarin — Sun, 05 Dec 2021 08:50:34 GMT

Hi, I tested with LOAD RecNo() as Field Autogenerate 5e8; AUTONUMBER Field; and it creates 500.000.000 different values. My PC doesn't supports more rows, maybe in a server or PC with more RAM you can do an higher test to check if autonumber starts again.

Or maybe with:

InitData: LOAD RecNo() as Field1 Autogenerate 1e5; Outer Join LOAD RecNo() as Field2 Autogenerate 1e5; FinalTable: LOAD Field1 &'_'& Field2 as Key; DROP Table InitData; AUTONUMBER Key;

And check if Key ends with 1e10 different values, check RAM available and consumed before/while doing this test or use lower values, the ones you need to simulate your different values in real data.

Re: Max number for autonumber

marcus_sommer — Sun, 05 Dec 2021 10:12:10 GMT

Like above mentioned the importance of distinct values relates to fields - to single fields and not any addition of them and in this regard there is no discrepancy between the max. number of distinct field-values and the max. value of an autonumber which is: 2,147,483,648 - both is the same (it's the only restriction in Qlik everything else depends on the available resources).

Applying autonumber to key-fields may have a positive impact on the UI performance for a certain datamodel but using it as a general measure goes in the wrong direction and you may gain the opposite effect. This means it will be always depending on the data-model and data-models which are benefiting from an autonumber might be not the most effective one for the required views and performances.

- Marcus