Overview

In the Etlworks Integrator, 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().getDataSet().getFileNameToRead();

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: 

importPackage(com.toolsverse.etl.core.task.common);

// 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;