Configuring Spoon scripting steps
You can use scripting steps to implement JavaScript features.
About this task
if/then/else
clause. You can set values
into the transform stream that are constants or are from a variable.Procedure
- Use JavaScript scripting if you need logic to set the values.
- In the Spoon example transform, duration is calculated by subtracting two dates from each other. The duration then determines whether a plan was on time.
- With the JavaScript scripting features, if you want information out of the Table Input rows, you must iterate to find the field you want. You cannot access the field directly, unless you alias the field in the Table Input step.
Example
var actualEnd;
var actualStart;
var plannedEnd;
var plannedStart;
var duration;
var valueDuration;
var valueOnTime;
// loop through the input stream row and get the fields
// we want to play with
for (var i=0;i<row.size();i++) {
var value=row.getValue(i);
// get the value of the field as a number
if (value.getName().equals("TRIACTUALENDDA")) {
actualEnd = value.getNumber();
}
if (value.getName().equals("TRIACTUALSTARTDA")) {
actualStart = value.getNumber();
}
if (value.getName().equals("TRIPLANNEDENDDA")) {
plannedEnd = value.getNumber();
}
if (value.getName().equals("TRIPLANNEDSTARTDA")) {
plannedStart = value.getNumber();
}
// these are the 'variables' in the stream that we want
// to update with the duration and ontime setting
// so we want the actual Value class not the value
// of the variable
if (value.getName().equals("DURATION")) {
valueDuration = value;
}
if (value.getName().equals("ONTIME")) {
valueOnTime = value;
}
}
// calculate the duration in days
duration = Math.round((actualEnd - actualStart) / (60*60*24*1000));
// calculate the duration in hours
// duration = (actualEnd - actualStart) / (60*60*1000);
// set the duration into the 'variable' in the row
valueDuration.setValue(duration);
// determine ontime and set the value into the
// 'variable' in the row stream
if ((actualEnd == null) || (plannedEnd == null))
valueOnTime.setValue("");
else if (actualEnd > plannedEnd)
valueOnTime.setValue("no");
else
valueOnTime.setValue("yes");
Select Test Script to make sure
that the JavaScript compiles. The Test Script and Preview steps
in Table Input cannot handle variables unless they are set. You can
set variables in the transform by using . This
makes more of the test function within Pentaho Spoon.For example, you can use
and set triActiveStartDA_MinDATE to to_date(‘20061201’, ‘YYYYmmdd’).If you are using column aliases when you are defining your query, you must use the same alias when you are looking up the column with getName.
The
following example, in the table input step, shows the select option:
SELECT mainProject.triProjectCalcEndDA ActualEndDate,
mainProject.triProjectActualStartDA ActualStartDate
If
you are looking up the value for ActualEndDate,
use the alias and not the column name from the database, as illustrated:
if (value.getName().equals("ActualEndDate")) {
actualEnd = value.getNumber();
}