Reverse AutoNumber OR UnAutoNumber

[syedalamdar]

Is there a reverse to AutoNumber? I find AutoNumber really helpful when dealing with large data sets and merging them together for ETL purposes but in order to reach the end goal, I have to drag the original values in a separate column. Given the translation of the Key Values and Number are already in the memory during the load script, I was wondering if there is a command that we can use to "Un-AutoNumber" at the end of the process.

Example: 30m Records

I have a table that just has directory paths for the entire firm. There is another Table that has access information as which groups in the firm have access to these paths. The third table has all the members of the multiple groups. The fourth Table has the last access times to folders with user information. I think we get the picture that if we merge all of these we will get a really slow / big app. To avoid this, I use the AutoNumber. In the end I ApplyMap to get the values back but I feel like there should be a way to "Un-AutoNumber". Just asking. 

Thanks in advance,

Syed

[marcus_sommer · Accepted solution]

Autonumber() itself couldn't be reversed. If you need the origin values you need to load them, too. By a single field as the source for the autonumber() it has no much benefits to apply it because you will just add an extra field to your datamodel (it could avoid calculations over the key-fields but at the moment I don't remember a usecase in which it would cause really trouble).

If there are multiple fields combined in an autonumber() it will have benefits even if you keep all of them within the datamodel. Essentially for this behaviour is the fact that the fields (system-tables) only store distinct values and reference with a binary pointer to the data-tables - unless the autonumber() because field-value and pointer-value are the same and therefore an autonumber() didn't need to be stored.

Quite important is that autonumber() is aimed as a performance-feature within a final application. In the ETL in beforehand it's rather seldom useful because you need to ensure that you would always control the load-order of these field(s) over all related tables. Surely there will be usecases in which it may work but trying to implement it means to add another level of complexity in the ETL workflow.

- Marcus

[simonaubert]

Hello @syedalamdar 

Please note that you can vote for this idea :
https://community.qlik.com/t5/Ideas/Reverse-Autonumber-function/idi-p/1765662#M4439

Best regards,

Simon