7 Replies Latest reply: Oct 15, 2013 9:51 AM by John Lifter RSS

    Create a autonumber field with a transformation

    Diamantis Archontoglou

      How can we create an auto-numbered sequentially field using a transformation?

        • Re: Create a autonumber field with a transformation
          Michael Tarallo

          Hello Diamantis,

           

          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.

           

          See screenshot:

           

          seq.png

           

          Regards,

           

          Mike T

            • Re: Create a autonumber field with a transformation

              utility.sequence is a function call, so you need pararentheses

               

              utility.sequence()

                • Re: Create a autonumber field with a transformation
                  Diamantis Archontoglou

                  Yes, just found it out my self. Thanks anyway !

                  • Re: Create a autonumber field with a transformation

                    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:

                    utility.store_integer("nextNumber",1).

                    • Use a function rule in the transform operator.
                    • Within your transfrom function code, code the call to the utility.sequene function:

                    utility.sequence(utility.retrieve_integer("nextNumber")).

                    • Then in the finalize function store the next sequence number in the persistent value:

                    utility.store_integer("newNumber",utiliity.sequence(utility.retrieve_integer("nextNumber"))).

                     

                     

                    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.

                  • Re: Create a autonumber field with a transformation
                    Jamie Hicks

                    Michael,

                     

                    Although you said to ignore the DATEIN input, I'm curious what happens when you do give an input. I'm looking for a way to autonumber over a dimension... basically with each change of the input, the sequence starts back over at 1.

                     

                    Thanks!

                      • Re: Create a autonumber field with a transformation
                        Kenneth Madsen

                        When we do this, we usually rely on sorted data and have something that looks like this:

                         

                        s=0

                        lastkey=nil

                        function transform(input)

                          output={}

                          if lastkey == nil or input.keycol ~= lastkey then

                             s=1

                             lastkey = input.keycol

                          else

                             s=s+1

                          end

                         

                        Outside of this, we sometimes bury the logic in the SQL for an input SQL Query, but this relies on Oracle specific analytic expressions.

                          • Re: Create a autonumber field with a transformation

                            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.

                            autonumber.png

                            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.