Data360 Analyze

I noticed using the Input Excel node there's the ability to pick-up an Input by either explicitly defining the file to pick-up, or to use the FilenameExpr which allows you to define an expression to use to pick-up the input file. This is really convenient since it allows me to conditionally define a filepath. However, when using the Output Excel node, there only seems to be the option to explicitly define the filepath and no equivalent "FilenameExpr" field. Is there another node I could use to emulate this piece?

For example, let's say I have several different filepaths as per below. Of these, there would be one path dedicated for each combination of DEV/UAT/PROD and Input/Output. I want to be able to give someone the ability to specify which "environment" they're running for (DEV/UAT/PROD) through a "Run Property" variable and be able to conditionally define which set of Input/Output paths I need to use within Data3Sixty Analyze. I can do this for Inputs right now by using FilenameExpr field, but since the Outputs don't have this field, I'm not sure how to conditionally define the path this way. Is there a way to do this?

-C:\RandomPathA

-C:\RandomPathB

-C:\RandomPathC

-C:\RandomPathD

-C:\RandomPathE

-C:\RandomPathF

As you indicate the Output Excel node requires a literal value to be specified for the 'File' property and there is no mechanism to provide a lookup of a Run property value to a corresponding file path and substitute the value into the node's 'File' property. You can use a more involved mechanism to result in conditional execution of one of a number of Output Excel nodes depending on the value of the Run property.

Attached is an example data flow which uses this approach.

A 'Choice' type Run property is used to define the required environment

The Environment property is configured with the required choices:

A Composite node defines the properties for each of the Dev/UAT/Prod paths:

These paths will be substituted into the 'File' property of the Output Excel node which is to be executed. For example using the following:

Within the Composite node there is some logic to determine the value of the Environment Run property and then, depending on its value, run the appropriate Output Excel node.

The first record of the data (separated out using the Head node) is used to kick-off the processing of the Transform node - which retrieves the value of the Run property (if the property is not set this now will generate an error).  A series of Split nodes are then used to 'steer' the record containing the Environment value to one of the Meta Check nodes. The Meta Check nodes validate the number of input records - only one Meta Check node will receive the record, the others will only have the metadata on their inputs. The Meta Check nodes are configured to enable their associated Output Excel node to run if there is one record - so only one of them runs, outputting the data to the specified file.

Note, the log level for the Meta Check nodes is configured to supress the usual warning indication that would be generated when the validation checks fail for the other two Meta Check nodes. When the Composite node is run it will indicate only some of the nodes were run - this is expected in this case.

You will need Data3Sixty 3.4.0 or above to import the attached .lna file.

Attached files

Variable Excel Output Path - 26 Jun 2019.lna

Thanks Adrian, I think this is definitely a good workaround for what I'm trying to do!

Hi

I need to do something similar, but a step further which is dynamically assign the File Location and File name to an output file in an 'output excel' node. I can define variables with values on the composite node and then use them in the 'output excel' node as per below screenshot but is there anyway during the execution of the graph to set/update the value of these variables on the fly based on some input data?

e.g. in a transform node change the value of  {{^Filename^}} variable to =  "NEW_File_Name.xlsx"

thanks

Scott

The values of Node properties and other property references (Data Flow properties and Run Properties) are effectively compiled into Constants when a data flow starts to run (is initalized). Once the data flow is actually running it is not possible to change the value of these Constants.

You could investigate using an Execute Data Flow node to run a child data flow and pass the value for the filename into the child data flow from a main data flow. The data to be written to the Excel file would need to be stored in a temp file. This is similar to the approach used in this post. You should ensure the temp file is unique if there is any possibility of multiple instances of the child data flow being run at the same time.