There are also a lot of samples in your local 'data-integration/samples/transformations' folder.
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.
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.
The Fields table contains a list of variables from your script including the ability to add metadata like a descriptive name.
- Get Variables button - Retrieves a list of variables from your script.
- Test script button - Use this button to test the syntax of your script.
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
For more details, read the migration 2.5->3.0 doc.
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 same snippet without compatibility switched on:
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:
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.
When compatibility is switched on, use setValue on the input field as follows (assuming field1 is a field in the input row):
setValue() takes all possible types that can be used in PDI (also String, Dates, ...).
You can use the following construction (to get something like nvl(a, '0')) with the compatibility switch on:
and you can also use:
which would replace the value of fieldName with the value of '1' if fieldName is null.
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.
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.
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).
Make sure to use the following construct:
Make sure to refrain from using the == operator for Strings
You can also use the following method if you want to ignore case differences:
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:
If you want to filter rows, i.e. remove rows from the output, you can set the trans_Status variable:
All rows matching the condition are removed from the output.