5 Replies Latest reply: Aug 14, 2013 7:24 AM by Jake Star RSS

    Append columns from Parameters

      I feel silly asking this - will probably slap myself when you show me how easy it is.

       

      I'm reading in an Excel file, then writing the data to a SQL table. In the SQL table, I have some additional columns that I want to populate.

       

      For sake of the example, let's call the additional columns ReportYear and ReportMonth. I want every record I write to have ReportYear = 2014 and ReportMonth = 8. I'm about to do this successfully in a Transform.

       

      However, what I want is for these values to be parameters somewhere, so that when I run the job again next month, I just need to update the ReportMonth parameter to 9, and all my dataflows can read the updated parameters.

       

      I've created these as parameters, but don't know how to read them in a transform. I also tried a lookup table but couldn't figure out how to use that in the script.

       

      Thanks!

        • Re: Append columns from Parameters

          You just have to create a variable:

           

          SET vReportMonth = 1;

           

          and then, in the load script:

           

          LOAD

               $(vReportMonth) as ReportMonth,

               ...

           

          Is this what you wanted?

          • Re: Append columns from Parameters

            In Expressor there are several ways to do this.  However, the concepts of parameters and parameterization are generally  used when changing the properties assigned to operators not for bringing values into datascript code (although they can be used that way).

             

            One approach to your use case is to use what are called persistent values.

             

            Expressor includes an embedded relational database management system that can be used to store values to be passed from dataflow to dataflow or between steps in a dataflow.  These values exist until you change them or destroy them, they do not disappear when the dataflow completes.

             

            In your example, you would manually set two persistent values, for the month and year.  Then code in the transform operator will pick up the values and use them to initialize attributes.

             

            Before you run the dataflow, open an Expressor command window and issue the command 'datascript'.  This will start the interactive datascript coding window.  Then issue commands similar to the following:

            • utility.store_integer("Month",8)
            • utility.store_integer("Year",2013)

             

            In the transform operator, retrieve the value of these values with code similar to the following.

            • month=utility.retrieve_integer("Month")
            • year=utility.retrieve_integer("Year")

             

            Then use month and year to initialize the new attributes.

             

            As long as the persistent values don't need updating, there is no need to reset them, simply run the dataflow.  Each month update the month value and each year update the year value.

             

            Another approach would be to use code to extract the month and year from the date on which the dataflow is running.  For example, the function datetime.timestamp will return the current date and time as a datetime value.  Then use the datetime.string function to extract the month and year values as strings and use these values to initialize the new attributes, converting to integers if desired.

              • Re: Append columns from Parameters

                Thanks to both of you. I get how to do it, but was hoping for a more elegant answer. The idea was to make this a bit more user-friendly, so that the operator of the workflow doesn't need to end up scripting anything. In other languages, I would prompt them for the parameters, or read them in from an external file. I'm going to end up with a few hundred workflows, and not all of them will always use the same values so an elegant solution was preferred.

                  • Re: Append columns from Parameters

                    Expressor does not have the concept of asking for input values as it is meant to run as a server process without a visible GUI.

                     

                    However, if you use the approach of extracting the month and year from the date on which the dataflow is run, the user does not need to provide any code or input values.  The code the dataflow developer creates does the work.

                     

                    If you want to read values in from a file, that too is possible.  Within the transform operator, in the initialize function, you read the external file, extract whatever values you want to insert into each record, and then in the transform function, use those values to initialize attributes.  To understand how to work with files, search out Lua documentation on the Web.

                     

                    If you purchase a license to Expressor, so that the dataflows may be executed from a script running in a command window (or in the background), there are other approaches, some of which would allow you to pass different values into selected dataflows.  With proper design, users would never need to input values, unless that is the intention of the developer.