Source Metadata References

This reference retrieves the fully qualified path for a row of data sourced from a file. As you are working with a dataset in the application, it can be helpful to know where the file from which each row of data originated. Using the $filepath function, you can generate columns of data early in your recipe to retain this useful information.

The following transforms might make file path information invalid or otherwise unavailable:

Single row formula

$filepath

'src_filepath'

Single row formula

RIGHTFIND(src_filepath, '\/', false, 0)

rightfind_src_filepath

Single row formula

SUBSTRING(src_filepath, rightfind_src_filepath + 1, LEN(src_filepath))

filename

rightfind_src_filepath

Delete selected columns

Single row formula

$filepath

'filepath'

Single row formula

$sourcerownumber

'source_row_number'

Multiple row formula

IF(PREV($filepath, 1) == $filepath, NULL(), $sourcerownumber)

$sourcerownumber

'start_of_file_offset'

Multiple row formula

FILL(start_of_file_offset, -1, 0)

$sourcerownumber

'filled_start_file_offset'

Single row formula

($sourcerownumber - filled_start_file_offset) + 1

'source_row_number_per_file'

filled_start_file_offset,start_of_file_offset

Delete selected columns

The $sourcerownumber variable is a reference to the row number in which the current row originally appeared in the source of the data.

Limitations:

Example:

The following example generates a new column containing the source row number for each row in the dataset, if available:

Single row formula

$sourcerownumber

'src_rownumber'

If you have already used the $filepath reference, as in the previous example, you can combine these two columns to create a unique key to the source of each row:

Single row formula

MERGE([src_filename,src_rownumber],'-')

'src_key'

The $col variable is a reference to the column that is currently being evaluated. This variable references the state of the current dataset, instead of the original source.

In the following example, all columns in the dataset that are of String type are converted to uppercase:

All

IF(ISMISMATCHED($col, ['String']), $col, UPPER($col))

In the above, the wildcard applies the edit to each column. Each column is tested to see if it is mismatched with the String data type. If mismatched, the value in the column ($col) is written. Otherwise, the value in the column is converted to uppercase (UPPER($col)).

You can create flow parameters that can be referenced in your recipe steps. In your step, you insert the parameter reference token such as the following:

${MyParameterName}

When the job is executed, this parameter reference is replaced with the corresponding value for it, which can be the default value or an override specified for the flow or the job. Flow parameters are specified through Flow View. For more information, see Manage Parameters Dialog.