Description
This is a modified version of the 'JavaScript Values' step that provides better performance and an easier, expression based user interface for building JavaScript expressions. This step also allows you to create multiple scripts for each step.
Please see also 'Migrating JavaScript from 2.5.x to 3.0.0' more more details on internals.
There are also a lot of samples in your local 'data-integration/samples/transformations' folder.
Please keep in mind that this step has performance disadvantages due to the fact that it is interpreting the JavaScript. Please see the other steps in the 'Scripting' section when performance is critical for you.
Java script functions
This section provides a tree view of your available scripts, functions, input fields and output fields.
- Transformation Scripts: displays a list of scripts you have created in this step
- Transformation Constants: a list of pre-defined, static constants including SKIP_TRANSFORMATION, ERROR_TRANSFORMATION, and CONTINUE_TRANSFORMATION
- Transformation Functions: contains a variety of String, Numeric, Date, Logic and specialized functions you can use to create your script. To add a function to your script, simply double-click on the function or drag it to the location in your script that you wish to insert it.
- Input Fields: a list of inputs coming into the step. Double-click or use drag and drop to insert the field into your script.
- Output Fields: a list of outputs for the step.
Java Script
This section is where you edit the script for this step. You can insert functions, constants, input fields, etc. from the tree control on the left by double-clicking on the node you wish to insert or by dragging the object onto the Java Script panel.
Fields
The Fields table contains a list of variables from your script including the ability to add metadata like a descriptive name.
Extras
- Get Variables button - Retrieves a list of variables from your script.
- Test script button - Use this button to test the syntax of your script.
Java script internal API objects
You can use the following internal API objects (for reference see the classes in the source):
- _TransformationName_: a String with the actual 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 actual data Object[]
FAQ
What does the compatibility switch do?
There are two version of the javascript engine: the 2.5 version and the 3 version. If "compatibility mode" is checked (and by default it is), javascript works like it did in version 2.5. Obviously the new version should be used if possible so uncheck "compatibility mode" if you can.
The big difference between the two versions is that in 2.5, value objects are directly modifiable and their type can be changed (a date variable can be converted into a string). This can cause subtle bugs (see migration 2.5->3.0 doc for more details). Because this is no longer possible in the 3.0 version, the javascript should also be faster.
For more details, read the migration 2.5->3.0 doc.
How to check for the existence of fields in rows (with compatibility on)?
The following snippet (using compatibility switched on) will let you check this. But keep in mind that you can not mix rows in PDI, all rows flowing over a single hop have to have the same number of fields, which have to be of the same name and type.
The snippet:
var idx = row.searchValueIndex("lookup");
if ( idx < 0 )
{
var lookupValue = 0;
}
else
{
var lookupValue = row.getValue(idx);
}
The same snippet without compatibility switched on:
var idx = getInputRowMeta().indexOfValue("lookup");
if ( idx < 0 )
{
var lookupValue = 0;
}
else
{
var lookupValue = row[idx];
}
How to add a new field in a row
Note up front that the order in which fields are added to a row is important. Always add fields in the same order to keep the structure of the row coherent.
Now to add a field:
- Define it as "var" in the source and add it as a field in the fields table in the lower half of the JavaScript dialog.
How to modify values (with compatibility off)
In 3.0+, the preferred way to change an input value (and potentially its type) is to create a new variable and output it using the "fields" list underneath the main javascript textarea. Then in a separate 'Select values' step, replace the old variable with the new one. While this is slightly less elegant than in 2.5, the code should be faster and safer.
Alternately (with improved error reporting after 4.1.0-GA) you can use the "Replace value 'Fieldname' or 'Rename To'" field. If this is yes, 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.
How to modify values (with compatibility on)
When compatibility is switched on, use setValue on the input field as follows (assuming field1 is a field in the input row):
field1.setValue(100);
setValue() takes all possible types that can be used in PDI (also String, Dates, ...).
How to use something like NVL in JavaScript?
You can use the following construction (to get something like nvl(a, '0')) with the compatibility switch on:
var a;
if ( fieldname.isNull() )
{
a = '0';
}
else
{
a = fieldName.getString();
}
and you can also use:
fieldName.nvl('1');
which would replace the value of fieldName with the value of '1' if fieldName is null.
Example of how to split fields
In a field I have merchant code containing numbers and characters, ex. "12345McDonalds". I want to split it but the field doesn't have a consistent layout and I want to split the first part which is numeric from the second part.
Use following piece of JavaScript, 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() is just to show the fields of course. After the outer for loop you could add code and name in new separate fields e.g.
Comparing values
All the values that are coming from the data row are Java objects, so a compare between values with "=", ">", "<" etc. will not work.
You need to use the compare methods that are specific to the Java object (see samples below).
Comparing String values
Make sure to use the following construct:
string.equals(otherString)
Make sure to refrain from using the == operator for Strings
You can also use the following method if you want to ignore case differences:
string.equalsIgnoreCase(otherString)
Comparing numeric values
Most values that are assigned in JavaScript are by default floating point values, even if you think you assign an integer.
Whatever may be, in case you are having trouble using == or switch/case on values that you know are integer in nature, 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
If you want to filter rows, i.e. remove rows from the output, you can set the trans_Status variable:
trans_Status = CONTINUE_TRANSFORMATION if (/* add your condition here */) trans_Status = SKIP_TRANSFORMATION
All rows matching the condition are removed from the output.
Useful external links
Blog "Squeezing the most out of the JavaScript Step in Pentaho Kettle":
http://type-exit.org/adventures-with-open-source-bi/2010/06/squeezing-the-most-out-of-the-javascript-step-in-pentaho-kettle/