Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Since Qlik Sense June 2018, AutoNumber is available as a script statement (and not only as a function). This subroutine automatically Autonumbers all keys in your data model.
SUB AutoAutoNumber
// Loop all tables in the data model, and
// create a list of all tables/fields
FOR AutoAutoNumber.t = 0 TO NoOfTables() - 1
AutoAutoNumber.TableName = TableName($(AutoAutoNumber.t));
// Loop all fields for single table
FOR AutoAutoNumber.f = 1 TO NoOfFields('$(AutoAutoNumber.TableName)')
[AutoAutoNumber.TableFields]:
LOAD
'$(AutoAutoNumber.TableName)' AS [AutoAutoNumber.Table],
FieldName($(AutoAutoNumber.f), '$(AutoAutoNumber.TableName)') AS [AutoAutoNumber.Field]
AUTOGENERATE (1)
;
NEXT // AutoNumber.f
NEXT // Autonumber.t
// Clean up variables
LET AutoAutoNumber.TableName = null();
LET AutoAutoNumber.t = null();
LET AutoAutoNumber.f = null();
// Get the list of key fields
[AutoAutoNumber.KeyFields]:
LOAD
[AutoAutoNumber.Field]
WHERE [AutoAutoNumber.NoOfTables] > 1
;
LOAD
[AutoAutoNumber.Field],
Count(AutoAutoNumber.Table) AS [AutoAutoNumber.NoOfTables]
RESIDENT [AutoAutoNumber.TableFields]
GROUP BY [AutoAutoNumber.Field]
;
// Clean up
DROP TABLE AutoAutoNumber.TableFields;
// Autonumber all detected key fields
FOR AutoAutoNumber.i = 0 TO NoOfRows('AutoAutoNumber.KeyFields') - 1
LET AutoAutoNumber.AutoNumberField = peek('AutoAutoNumber.Field', $(AutoAutoNumber.i), 'AutoAutoNumber.KeyFields');
AutoNumber [$(AutoAutoNumber.AutoNumberField)] USING '$(AutoAutoNumber.i)';
NEXT // AutoNumber.i
// Clean up
DROP TABLE AutoAutoNumber.KeyFields;
LET AutoAutoNumber.AutoNumberField = null();
LET AutoAutoNumber.i = null();
END SUB
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.