Configuring Spoon scripting steps

You can use scripting steps to implement JavaScript features.

About this task

You can use it for specific data manipulations on the input stream that cannot be done with the Calculator. You can calculate the duration or set values into the stream, which is based on other values with an if/then/else clause. You can set values into the transform stream that are constants or are from a variable.

Procedure

  1. Use JavaScript scripting if you need logic to set the values.
  2. 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.
  3. 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

The JavaScript scripting example details how to obtain and set the variables.
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 Edit > Set Environment Variables. This makes more of the test function within Pentaho Spoon.

For example, you can use Edit > Set Environment Variables 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();
}