autonumber - script function
This script function returns a unique integer value for each distinct evaluated value of expression encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.
Syntax:
autonumber(expression[ , AutoID])
Arguments:
Argument | Description |
---|---|
AutoID |
In order to create multiple counter instances if the autonumber function is used on different keys within the script, the optional parameter AutoID can be used for naming each counter. |
Example: Creating a composite key
In this example we create a composite key using the autonumber function 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 a preceding load which creates a composite key from the Region, Year and Month fields.
The resulting table looks like this:
Region | Year | Month | Sales | RYMkey |
---|---|---|---|---|
North | 2014 | May | 245 | 1 |
North | 2014 | May | 347 | 1 |
North | 2014 | June | 127 | 2 |
South | 2014 | June | 645 | 3 |
South | 2013 | May | 367 | 4 |
South | 2013 | May | 221 | 4 |
In this example you can refer to the RYMkey, for example 1, instead of the string 'North2014May' if you need to link to another table.
Now we load a source table of costs in a similar way. The Region, Year and Month fields are excluded in the preceding load to avoid creating a synthetic key, we are already creating a composite key with the autonumber function, linking the tables.
Now we can add a table visualization to a sheet, and add the Region, Year and Month fields, as well as Sum measures for the sales and the costs. The table will look like this:
Region | Year | Month | Sum([Sales]) | Sum([Costs]) |
---|---|---|---|---|
Totals | - | - | 1952 | 784 |
North | 2014 | June | 127 | 199 |
North | 2014 | May | 592 | 56 |
South | 2014 | June | 645 | 64 |
South | 2013 | May | 588 | 465 |