Skip to main content
Pentaho Documentation

Modified Java Script Value

The Modified Java Script Value step provides a user interface for building JavaScript expressions that you can use to modify your data. The code you type in the script area is executed once per each row coming to the step. This step also allows you to create multiple scripts for in a single step.

This step is not a proper input step, and does not execute without an input. This step is a modified version of the JavaScript Values step, which has been deprecated and removed from PDI. 

General

PDI_Modified_JavaScript_dlg.png

  • Step Name: Specify the unique name of the Modified Java script value step on the canvas. You can customize the name or leave it as the default.

The Modified Java Script value step features two panes and a table. Each is described below.

Java Script Functions Pane

The Java script functions pane contains a tree view of scripts, constants, functions, input fields, and output fields as follows: 

  • Transform Scripts: Scripts you have created in this step.
  • Transform Constants: Pre-defined, static constants that control what happens to the data rows. You must  assign a constant value to the trans_Status variable. To use these constants, you must first set the trans_Status variable to CONTINUE_TRANSFORMATION at the beginning of the script, so that the variable assignment is made to the first row being processed. Otherwise, any subsequent assignments to the trans_Status variable are ignored. Double-click a constant to add it to the Java script pane. 

The constants are:

  • CONTINUE_TRANSFORMATION: Includes the current row in the output row set.
  • SKIP_TRANSFORMATION: Excludes the current row from the output row set and continues processing on the next row.
  • ERROR_TRANSFORMATION: Excludes the current row from the output row set, generates an error, and any remaining rows are not processed. 
  • ABORT_TRANSFORMATION: Excludes the current row from the output row set, and any remaining rows are not processed, but does not generate an error. (This constant does not display in the PDI client, but can be used in your script)
  • Transform Functions: String, numeric, date, logic, special, and file functions you can use in scripts. These included functions are implemented in Java and execute faster than JavaScript functions. Each function has a sample script demonstrating its use. Double-click the function to add it to the Java script pane. Right-click and choose Sample to add the sample to the Java script pane.

Not all JavaScript functions are listed here. You can use functions not included in this list. 

  • Input Fields: Input fields for the step. 
  • Output Fields: Output fields for the step.

Java Script Pane

The Java script pane is the editing area for writing your code. You can insert constants, functions, input fields, and output fields from the Java script functions pane on the left by double-clicking the node you want to insert or by using drag-and-drop to place the object onto the Java Script panel. 

Field Description
Position Display the line number and position of the cursor.
Compatibility Mode

Select the Compatibility mode option to use the 2.5 version of the JavaScript engine. When this option is not selected (default), the step uses version 3 of the JavaScript engine.

In the 2.5 engine, value objects are directly modifiable and their type can be changed. For example, you can convert a date variable into a string. This is not supported in the 3.0 engine, so the JavaScript should run faster. For more details on the different versions, see Migrating JavaScript from 2.5.x to 3.0.0.

PDI uses the Rhino engine from Mozilla.

Optimization level

Select the level of JavaScript optimization. The values are:

  • 1: JavaScript runs in interpreted mode.
  • 0: No optimizations are performed.
  • 1-9: All optimizations are performed. 9 performs the most optimization with faster script execution, but compiles slower. The default is 9.

Script Types

PDI_ModifiedJavascriptStep_JavascriptPane_context_Menu.png

You can right-click a tab in the Java script pane to open a context menu with the following commands:

  • Add new – Add a new script tab.
  • Add copy – Add a copy of the existing script in a new tab.
  • Set Transform Script - Specify the script to execute for each incoming row. Only one tab can be set as a transform script. The first tab is a transform script by default.
  • Set Start Script - Specify the script to execute before processing the first row.
  • Set End Script – Specify the script to execute after the last row has been processed.
  • Remove Script Type - Specify to not execute the script. The script tab is not removed. To remove a script tab, click the Close button (the red “X”) and choose Yes to delete the script tab.

The icon for the script type displays on the tab to denote the type of script on the tab. To rename a script tab, right-click the tab name in the Transform Scripts section of the Java script functions pane, choose Rename, and enter the new name.

Fields Table

The Fields table contains a list of variables from your script, and enables you to add metadata to the fields, like descriptive names.

Field Description
Fieldname Specify the name of the incoming field.
Rename to Specify a new name for the incoming field.
Type Specify a data type for the output field.
Length Specify the length of the output field.
Precision Specify the precision value of the output field.
Replace value ‘Fieldname’ or ‘Rename to’

Specify whether to replace the value of the selected field with another value or to rename a field.

The values are Y (Yes) and N (No).

Get variables

Retrieve a list of JavaScript variables from your script.

JavaScript variables are not the same as PDI variables.

Test Script Test the syntax of your script, and displays the Generate Rows dialog box with a set of rows for testing.

Modify Values

To change an input value, enter the value in the Rename to field, and set the Replace value 'Fieldname' or 'Rename To' field to Y. The Rename to field (or if this is blank, the Fieldname field) is used to lookup an existing field and replace its value and metadata type. If the specified field does not exist in the input stream, an error is passed onto the next step indicating that the field to be replaced could not be found.

The following example changes the value of the field1 field in the input row with Compatibility mode selected:

field1.setValue(100);

setValue() takes all possible types that can be used in PDI.

JavaScript Internal API Objects

You can use the following internal API objects (for reference see the classes in the source):

  • _TransformationName_: A String with the transformation name
  • _step_: The actual step instance of org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod
  • rowMeta: The actual instance of org.pentaho.di.core.row.RowMeta
  • row: The actual instance of the data Object[]

Examples

Check for the Existence of Fields in a Row

The following code examples check for the existence of fields in rows:

var idx = getInputRowMeta().indexOfValue("lookup");
if ( idx < 0 )
{
   var lookupValue = 0;
}
else
{
   var lookupValue = row[idx];
}

With Compatibility mode selected:

var idx = row.searchValueIndex("lookup");
if ( idx < 0 )
{
   var lookupValue = 0;
}
else
{
   var lookupValue = row.getValue(idx);
}

 You cannot mix rows in PDI. All rows flowing over a single hop must have the same name, type, and number of fields.

Add a New Field in a Row

Fields must be added to the rows in the same order to keep the structure of the row coherent. To add a field, define it as var in the Java script pane, and add it as a field in the Fields table. 

Use NVL in JavaScript

NVL (replace null value function) lets you replace a null (returned as a blank) with a string in the results of a query. 

Select Compatibility mode and use the following code to replace the value of fieldName with the value of '1', if fieldName is null:

var a;
if ( fieldname.isNull() )
{
    a = '0';
}
else
{
    a = fieldName.getString();
}

You can also use:

fieldName.nvl('1');

Split Fields

Select Compatibility mode and use the following code to split a field containing numbers and characters, with inconsistent layout, and where the first part is numeric and the second part is characters (where Merchant_Code is the name of the input field):

java;
 
var str = Merchant_Code.getString();
 
var code = "";
var name = "";
 
for (i = 0; i < str.length(); i++ )
{
    c = str.charAt(i);
    if ( ! java.lang.Character.isDigit(c) )
    {
        code = str.substring(0, i);
        name = str.substring(i);
        Alert("code="+code+", name="+name);
        break;
    }
}

The Alert() displays the name of the fields. 

Comparing values

Values coming from the data row are Java objects, so you must use the compare methods that are specific to the Java object. For example, a compare between values with the operators "=", ">", "<" does not work.

The following examples are values that require the use of compare methods:

  • String values
  • Numeric Values
  • Filter Rows

String values

Use the following method to compare strings:

string.equals(otherString)

Do not use the == operator for strings.

Use the following method to ignore case differences:

string.equalsIgnoreCase(otherString)

Numeric Values

Most values that are assigned in JavaScript are floating point values by default, even if you think you have assigned an integer value. If you are having trouble using == or switch/case on values that you know are integers, use the following constructs:

parseInt(num)==parseInt(num2)

or 

switch(parseInt(valuename))
{
case 1:
case 2:
case 3:
 strvalueswitch = "one, two, three";
 break;
case 4:
 strvalueswitch = "four";
 break;
default:
 strvalueswitch = "five";
}

Filter Rows

To filter rows (remove the rows from the output for example) set the trans_Status variable as follows:

trans_Status = CONTINUE_TRANSFORMATION
if (/* add your condition here */) trans_Status = SKIP_TRANSFORMATION

 All rows matching the specified condition are removed from the output.

Sample Transformations

The design-tools/data-integration/samples/transformations directory contains the following example KTRs that show how to use this step:

  • JavaScript - Access environment variables
  • JavaScript - Add 2 and a half seconds to a Date
  • JavaScript - Base64 Decoder and Encoder
  • JavaScript - create new rows
  • JavaScript - date to string conversion
  • JavaScript - dialog
  • JavaScript - extract date from filename
  • JavaScript - Get the filename from a path
  • JavaScript - Hex to Integer conversion
  • JavaScript - parse Debet-Credit prefix in numbers
  • JavaScript - Parse XML
  • JavaScript - process all fields per row
  • JavaScript - replace values in a string with other values
  • JavaScript - Skip rows after x rows
  • JavaScript - Split String
  • JavaScript - String to Number to String conversion
  • JavaScript - Strip line feeds from text
  • JavaScript - truncate a date
  • JavaScript - Access database connection metadata