Description

Important : This step is no longer available as of version 3.0. This step is replaced by the Modified JavaScript step. Most functions described below still work in compatibility mode of the Modified JavaScript step, but you do well to check out the Migration guide anyway.

Script Values type allows you to perform complex calculations using JavaScript. The JavaScript engines used are :

Rhino 1.5R5 in version 3.0
Rhino 1.7R1 in version 3.1

Options

Option	Description
Step name	Name of the step; this name has to be unique in a single transformation.
Java Script	Specify the script you want to run
Fields	The fields to add to the output steam
Insert fields button	Inserts the fields and the standard method to grab the value of the field
Test script button	Tests whether or not the script compiles
Get variables button	Gets the newly created variables and inserts them into the Fields grid.

Value functions

This is a list of functions that you can use to manipulate values:

Function	Description
Value Clone()	Builds a copy of a value and returns a Value.
void setName(String name)	Sets the name of a value.
String getName() .	Get the name of a value
void setValue(double num)	Set the value to a floating point value.
void setValue(String str)	Set the value to a string value.
void setValue(Date dat)	Set the value to a Date value.
void setValue(boolean bool)	Set the value to a Boolean value.
void setValue(long l)	Set the value to an integer value.
void setValue(Value v)	Set the value to the value contained in another field.
double getNumber()	Gets the value of a field as a floating point value.
String getString()	Gets the value of a field as a textual string representation.
int getStringLength()	Gets the length of the string representation.
Date getDate()	Gets the value of the field as a date value.
boolean getBoolean()	Gets the value of a field as a Boolean. *Note*: String "Y" or "true" is converted to true. Numeric value 0 is converted to false, everything else is true.
long getInteger()	Gets the value of a field as an integer. *Note*: Date fields are converted to the number of milliseconds since January 1st 1970 00:00:00 GMT.
boolean isEmpty()	If the value has no type, this function returns true.
boolean isString()	If the value is of type String, this function returns true.
boolean isDate()	If the value is of type String, this function returns true.
boolean isNumber()	If the value is of type Number, this function returns true.
boolean isBoolean()	If the value is of type Boolean, this function returns true.
boolean isInteger()	If the value is of type Integer, this function returns true.
boolean isNumeric()	If the value is of type Number or Integer, this function returns true.
String toString()	Returns the textual representation of the value.
String toString(boolean pad)	Returns the textual representation of the value, padded to the length of the string if pad = true.
String toStringMeta()	Returns the meta-data information of the value as a string.
void setLength(int l)	Sets the length of the value.
void setLength(int l, int p)	Sets the length and precision of the value.
int getLength()	Returns the length of the value.
int getPrecision()	Returns the precision of the value.
void setPrecision(int p)	Sets the precision of a value.
String getTypeDesc()	Returns the description of a value as a string. (e.g. "String", "Number", "Date", "Integer", "Boolean").
void setNull()	Sets the value to Null.
void clearNull()	Removes the null setting.
void setNull(boolean n)
boolean isNull()	Returns true if the value is null.
int compare(Value v)	Compares two values and returns 1 if the first value is larger than the second, -1 if it is smaller and 0 if they are equal.
boolean equals(Object v)	Compares two values and returns true if the two values have the same value.
int hashCode()	Returns a signed 64 values representing the value in the form of a hash code.
Value negate()	If the value is numeric, multiplies the value by -1, in all other cases it doesn't do anything.
Value and(Value v)	Calculates the bitwise AND of two integer values.
Value xor(Value v)	Calculates the bitwise XOR of two integer values.
Value or(Value v)	Calculates the bitwise OR of two integer values.
Value bool_and(Value v)	Calculates the boolean AND of two boolean values.
Value bool_or(Value v)	Calculates the boolean OR of two boolean values.
Value bool_xor(Value v)	Calculates the boolean XOR of two boolean values.
Value bool_not()	Calculates the boolean NOT of a boolean value.
Value greater_equal(Value v)	Compares two values and sets the first to true if the second is greater or equal to the first.
Value smaller_equal(Value v)	Compares two values and sets the first to true if the second is smaller or equal to the first.
Value different(Value v)	Compares two values and sets the first to true if the second is different from the first.
Value equal(Value v)	Compares two values and sets the first to true if the second is equal to the first.
Value like(Value v)	Sets the first value to true if the second string is part of the first.
Value greater(Value v)	Compares two values and sets the first to true if the second is greater than the first.
Value smaller(Value v)	Compares two values and sets the first to true if the second is smaller than the first.
Value minus(double v) Value minus(long v) Value minus(int v) Value minus(byte v) Value minus(Value v)	Subtracts v from the field value.
Value plus(double v) Value plus(long v) Value plus(int v) Value plus(byte v) Value plus(Value v)	Adds v to the field value.
Value divide(double v) Value divide(long v) Value divide(int v) Value divide(byte v) Value divide(Value v)	Divides the field value by v.
Value multiply(double v) Value multiply(long v) Value multiply(int v) Value multiply(byte v) Value multiply(Value v)	Multiplies the field value by v. *Note*: Strings can be multiplied as well: the result is v times the string concatenated.
Value abs()	Sets the field value to the -field value if the value was negative.
Value acos()	Sets the field value to the cosine of the number value.
Value asin()	Sets the field value to the arc sine of the number value.
Value atan()	Sets the field value to the arc tangents of the number value.
Value atan2(Value arg0) Value atan2(double arg0)	Sets the field value to the second arc tangents of the number value.
Value ceil()	Sets the field value to the ceiling of a number value.
Value cos()	Sets the field value to the cosine of a number value.
Value cosh()	Sets the field value to the hyperbolic cosine of a number value.
Value exp()	Sets the field value to the exp of a number value.
Value floor()	Sets the field value to the floor of a number value.
Value initcap()	Sets the all first characters of words in a string to uppercase. "matt casters" -> "Matt Casters"
Value length()	Sets the value of the field to the length of the String value.
Value log()	Sets the field value to the log of a number value.
Value lower()	Sets the field value to the string value in lowercase.
Value lpad(Value len) Value lpad(Value len, Value padstr) Value lpad(int len) Value lpad(int len, String padstr)	Sets the field value to the string value, left padded to a certain length. Default the padding string is a single space. Optionally, you can specify your own padding string.
Value ltrim()	Sets the field value to the string, without spaces to the left of the string.
Value mod(Value arg) Value mod(double arg0)	Sets the value to the modulus of the first and the second number.
Value nvl(Value alt)	If the field value is Null, set the value to alt.
Value power(Value arg)
Value power(double arg0)	Raises the field value to the power arg.
Value replace(Value repl, Value with) Value replace(String repl, String with)	Replaces a string in the field value with another.
Value round()	Rounds the field value to the nearest integer.
Value rpad(Value len) Value rpad(Value len, Value padstr) Value rpad(int len) Value rpad(int len, String padstr)	Sets the field value to the string value, right padded to a certain length. Default the padding string is a single space. Optionally, you can specify your own padding string.\|
Value rtrim()	Remove the spaces to the right of the field value.
Value sign()	Sets the value of the string to -1, 0 or 1 in case the field value is negative, zero or positive.
Value sin()	Sets the value of the field to the sine of the number value.
Value sqrt()	Sets the value of the field to the square root of the number value.
Value substr(Value from, Value to) Value substr(Value from) Value substr(int from) Value substr(int from, int to)	Sets the value of the field to the substring of the string value.
Value sysdate() .	Sets the field value to the system date
Value tan(Value args[])	Sets the field value to the tangents of the number value.
Value num2str() Value num2str(String arg0) Value num2str(String arg0, String arg1) Value num2str(String arg0, String arg1, String arg2) Value num2str(String arg0, String arg1, String arg2, String arg3)	Converts a number to a string. Arg1: Decimal separator (either . or ,) Arg2: Grouping separator (either . or ,) Arg3: Currency symbol For example converting value: 1234.56 using num2str("###,##0.00", ",", ".") gives 1.234,56 .23 using num2str("###,##0.00", ",", ".") gives 0,23 1234.56 using num2str("000,000.00", ",", ".") gives 001.234,56
Value dat2str() Value dat2str(String arg0) Value dat2str(String arg0, String arg1)	Converts a date into a string. Arg0: format pattern, see also Number Formats Arg1: localized date-time pattern characters (u, t)
Value num2dat()	Converts a number to a date based upon the number of milliseconds since January 1st, 1970 00:00:00 GMT.
Value str2dat(String arg0) Value str2dat(String arg0, String arg1)	Converts a string to a date. Arg0: format pattern, see also Number Formats Arg1: localized date-time pattern characters (u, t)
Value str2num() Value str2num(String arg0) Value str2num(String arg0, String arg1) Value str2num(String arg0, String arg1, String arg2) Value str2num(String arg0, String arg1, String arg2, String arg3)	Converts a string into a number. Arg0: format pattern, see also Number Formats Arg1: Decimal separator (either . or ,) Arg2: Grouping separator (either . or ,) Arg3: Currency symbol
Value dat2num()	Converts a date into a number being the number of milliseconds since January 1st, 1970 00:00:00 GMT.
Value trim()	Remove spaces left and right of the string value.
Value upper()	Sets the field value to the uppercase string value.
Value e()	Sets the value to e
Value pi()	Sets the value to p
Value add_months(int months)	Adds a number of months to the date value.
Value last_day()	Sets the field value to the last day of the month of the date value.
Value first_day()	Sets the field value to the first day of the month of the date value.
Value trunc() Value trunc(double level) Value trunc(int level)	Set the field value to the truncated number or date value. Level means the number of positions behind the comma or in the case of a date, 5=months, 4=days, 3=hours, 2=minutes, 1=seconds, 0=miliseconds
Value hexEncode()	Encode a String value in its hexadecimal representation. E.g. If value is a string "a", the result would be "61".
Value hexDecode()	Decode a String value from its hexadecimal representation. E.g. If value is a string "61", the result would be "a". If the input string value is odd a leading 0 will be silently added.

Examples

Note: see also the example series in the samples/transformations folder:

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

Remember the previous row

Note: Since PDI 3.2 you can use the Analytic Query step (peek forward and backwards across rows) and do no need this JavaScript anymore.

Sometimes it can be useful to know the value of the previous row. This can be accomplished by this piece of code:

var prev_row;
if (prev_row == null) prev_row = row;
...
String previousName = prev_row.getString("Name", "-");
...
prev_row = row;

Note that row is a special field that contains all values in the current row.

Sets the location name of an address to uppercase

location.upper();

Extract information from a date field

// Year/Month/Day representation:
ymd = date_field.Clone().dat2str("yyyy/MM/dd").getString();
// Day/Month/Year representation:
dmy = date_field.Clone().dat2str("dd/MM/yyyy").getString();
// Year/Month :
ym = date_field.Clone().dat2str("yyyy/MM").getString();
// Long description of the month in the local language:
month_long_desc=
date_field.Clone().dat2str("MMMM").initcap().getString();
// Week of the year (1-53)
week_of_year = date_field.Clone().dat2str("w").getInteger();
// day of week, short description (MON-SUN)
day_of_week_short_desc
=date_field.Clone().dat2str("EEE").upper().getString();
// Day of the week (Monday-Sunday)
day_of_week_desc =
date_field.Clone().dat2str("EEEE").initcap().getString();
// Day of week (1-7)
day_of_week = date_field.Clone().dat2str("F").getInteger();

Note: If you don't use Clone(), the original value will be overwritten by the methods that work on the Kettle Values.