You can do so using the utility.sequence() function and mapping that to an integer attribute in the output.
This is found in the Utility function list.
Ignore the DATEIN input parameter (seen in the screenshot) - that was there from a previous example I was using. You do not need an input parameter to use this function.
Note that utility.sequence takes an optional argument, which is the number from which to start. This allows you to continue numbering in sequence over multiple executions of your dataflow, which is what a database would do with an auto-increment column. To take advantage of this functionality, you should use a persistent value to store the next number in the auto-increment sequence.
- For example, before running the dataflow for the first time, open an expressor command window, start the datascript command line interpreter and set the starting value for the sequence. Such as:
- Use a function rule in the transform operator.
- Within your transfrom function code, code the call to the utility.sequene function:
- Then in the finalize function store the next sequence number in the persistent value:
Note how in the finalize function, you must code the retrieve_integer function exactly as you did in the transform function. That is, you need to pass the same start argument call.
When we do this, we usually rely on sorted data and have something that looks like this:
if lastkey == nil or input.keycol ~= lastkey then
lastkey = input.keycol
Outside of this, we sometimes bury the logic in the SQL for an input SQL Query, but this relies on Oracle specific analytic expressions.
As noted above, utility.sequence is the way to add an autonumber field to a record. In the following image, note how the autonumber value can be stored as a persistent value so that it is possible to keep the sequence going over multiple runs of the dataflow.
Jamie added a different requirement, that the number restarts when the value of a dimension column changes. Ken's solution depends on the records being ordered by the dimension column value. If the records are sorted or not, an aggregate operator can be used to provide the processing Jamie requires, but the suitability of this approach depends on the number of records being processed. Whether the records are sorted on not, Expressor must maintain a table containing all the records with the same dimension column value and then emit these records adding the autonumber field.
If the records are sorted then use the aggregate function to store each record in a datascript table and the iterative return in the result function to emit each record with an increasing autonumber attribute that starts at one for each group.
If the records are not sorted, then the aggregate operator will first collect and group all the records, then you do the same processing in the aggregate and result functions.