AutoNumber
This statement creates a unique integer value for each distinct evaluated value in a field encountered during the script execution.
You can also use the autonumber - script function function inside a LOAD statement, but this has some limitations when you want to use an optimized load. You can create an optimized load by loading the data from a QVD file first, and then using the AutoNumber statement to convert values to symbol keys.
Syntax:
AutoNumber fieldlist [Using namespace] ]
Arguments:
Argument | Description |
---|---|
fieldlist |
A comma-separated list of the fields where the values should be replaced by a symbol key. You can use wildcard characters ? and * in the field names to include all fields with matching names. You can also use * to include all fields. You need to quote field names when wildcards are used. |
namespace |
Using namespace is optional. You can use this option if you want to create a namespace, where identical values in different fields share the same key. If you do not use this option all fields will have a separate key index. |
Limitations:
When you have several LOAD statements in the script, you need to place the AutoNumber statement after the final LOAD statement.
Example:
In this example we replace field values with symbol table keys using the AutoNumber statement to conserve memory. The example is brief for demonstration purpose, but would be meaningful with a table containing a large number of rows.
Region | Year | Month | Sales |
---|---|---|---|
North | 2014 | May | 245 |
North | 2014 | May | 347 |
North | 2014 | June | 127 |
South | 2014 | June | 645 |
South | 2013 | May | 367 |
South | 2013 | May | 221 |
The source data is loaded using inline data. Then we add an AutoNumber statemenet with the Region, Year and Month fields.
The resulting table would look like this:
Region | Year | Month | Sales |
---|---|---|---|
1 | 2 | 1 | 245 |
1 | 2 | 1 | 347 |
1 | 2 | 2 | 127 |
2 | 2 | 2 | 645 |
2 | 1 | 1 | 367 |
2 | 1 | 1 | 221 |