Overview

In Etlworks, you can use the per-field Mapping to calculate the field value.  

Use Cases

Below are common use-cases:

• Static column value

• Current date/ time as a column value

• Current date/ time formatted as String as a column value

• Source file name as a column value

• Source file date and size as a column value

Process

Static column value 

When creating a Mapping for the Destination column, enter the static column value as a value function.

Step 1. Create a Mapping where the source field doesn't exist, and the destination field is a calculated field.

Step 2. Click the Edit field value function (pen) icon and enter the field function:

'static value'

If the column value is a string, you must enclose it in single or double quotes, like this: 'static value'.

Current date/ time as a column value

When creating a Mapping for the destination column, just enter new java.util.Date() as a value function.

Step 1. Create a Mapping where the source field doesn't exist, and the destination field is a calculated field.

Step 2. Click the Edit field value function (pen) icon and enter the field function:

new java.util.Date();

Step 3. Go to MAPPING and click Field Data Type. Select DATE or TIMESTAMP for the Data type. Optionally, enter a Database specific type.

Current date/ time formatted as String as a column value

When the current date/ time is formatted as String as a column value, follow the steps below:

Step 1. Create a Mapping where the source field doesn't exist, and the destination field is a calculated field.

Step 2. Click the Edit field value function (pen) icon and enter the field function:

Utils.date2Str(new java.util.Date(), 'yyyyMMdd');

Available Date/Time Formats:

• Year: yyyy or yy
• Month: MM
• Day: dd
• Hours: HH
• Minutes: mm
• Seconds: ss

Example: MM/dd/YYYY HH:mm:ss

Source file name as a column value

Step 1. Create a Mapping where the source field doesn't exist, and the destination field is a calculated field.

Step 2. Click the Edit field value function (pen) icon and enter the field function:

destination.getSourceToUse().getLinkedSourceName() == null ?
destination.getSourceToUse().getDataSet().getFileNameToRead() : 
scenario.getSources().get(destination.getSourceToUse().getLinkedSourceName().toUpperCase()).
getDataSet().getFileNameToRead()

Note that the fields function checks whether the source associated with the destination has its own linked source. This is necessary when the Force Streaming option is enabled.

Force Streaming is a setting that enhances transformation performance when reading data from non-streamable sources such as JSON, XML, HTML, and PDF files. When enabled, the transformation creates a proxy source that streams data to the destination. However, this proxy source does not have an associated file, so the fields function must follow the link to locate the original source with the actual file.

Source file date and size as a column value

Step 1. Create a Mapping where the source field doesn't exist, and the destination field is a calculated field.

Step 2. Click the Edit field value function (pen) icon and enter the field function: 

var javaImports = new JavaImporter(com.toolsverse.etl.core.task.common); 

with (javaImports) {

// get the map of files<name, date> from flow config
var files = etlConfig.getValue('files');

// if map of files doesn't exist yet instantiate it and add to the flow config
if (files == null) {
    files = new java.util.concurrent.ConcurrentHashMap();
    etlConfig.setValue('files', files);
}

// get the filename from the source
var fileName = destination.getSourceToUse().getDataSet().getFileNameToRead();

// get the date of the file by name
var fileDate = files.get(fileName);

// if the date is not found
if (fileDate == null) {
   // get the list of files matching the filename from the source connection
   var listOfFiles = FileManagerTask.list(etlConfig, 
      destination.getSourceToUse().getConnectionName(), fileName);

   // if the list is not empty get the first file in the list (there is always one) 
   if (listOfFiles != null && !listOfFiles.isEmpty()) {
      // convert date in ms to string using the following format: MM/dd/yyyy HH:mm:ss
      fileDate = Utils.date2Str(new java.util.Date(
      listOfFiles.get(0).getLastModified()), "MM/dd/yyyy HH:mm:ss");

      // note: you can get file size as listOfFiles.get(0).getSize()

      // add date to the map
      files.put(fileName, fileDate);
   } 
}

// return date
value = fileDate;
}