Articles in this section

Calculated Fields in Mapping

  • Updated
Follow

Overview

In Etlworks, a mapping does not have to copy data directly from source to destination.

You can calculate values dynamically for any destination field using a field value function.

This is useful when the value:

  • Does not exist in the source 
  • Needs to be generated at runtime 
  • Depends on system metadata (file name, date, etc.)

How it works

For any destination field:

1. Create a mapping (source field can be empty) 

2. Click the Edit field value function (pen icon) 

file-name.png

3. Enter a Java/Nashorn expression that returns the value

The result of this expression becomes the field value.

Common examples

Static value

Use this when the field should always contain the same value.

'static value'

Notes:

  • Strings must be wrapped in quotes 
  • Works for flags, statuses, constants, etc.

Current date or timestamp

Use this when you need the execution time.

new java.util.Date();

Important:

  • Set the Field Data Type to DATE or TIMESTAMP 
  • Optionally specify a database-specific type

Current date as formatted string

Use this when the destination expects a string instead of a date.

var sdf = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
value = sdf.format(new java.util.Date());

Common format tokens:

  • Year: yyyy or yy 
  • Month: MM 
  • Day: dd 
  • Hour: HH 
  • Minute: mm 
  • Second: ss
  • Milliseconds: .SSS

Source file name

Use this when you need to track which file produced the record.

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

Why this is needed:

  • When Force Streaming is enabled, Etlworks may use a proxy source 
  • The proxy does not have a file 
  • This logic resolves the original source file

Excel worksheet name

Same idea as file name, but for Excel worksheets:

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

Source file date and size

Use this when you need file metadata such as last modified date or size.

var files = etlConfig.getValue('files');

if (files == null) {
    files = new java.util.concurrent.ConcurrentHashMap();
    etlConfig.setValue('files', files);
}

var fileName = destination.getSourceToUse().getDataSet().getFileNameToRead();
var fileDate = files.get(fileName);

if (fileDate == null) {

   var listOfFiles = com.toolsverse.etl.core.task.common.FileManagerTask.list(
      etlConfig,
      destination.getSourceToUse().getConnectionName(),
      fileName
   );

   if (listOfFiles != null && !listOfFiles.isEmpty()) {

      fileDate = com.toolsverse.util.Utils.date2Str(
        new java.util.Date(listOfFiles.get(0).getLastModified()),
        "MM/dd/yyyy HH:mm:ss"
      );

      // file size:
      // listOfFiles.get(0).getSize()

      files.put(fileName, fileDate);
   }
}

value = fileDate;

What this does:

  • Retrieves file metadata from the source connection 
  • Caches results for performance 
  • Returns formatted last modified date

Key takeaway

Mappings in Etlworks are not just field-to-field copies.

They are a lightweight transformation layer where you can compute values inline without adding extra steps to the flow.

Related articles