Skip to main content
Pentaho Documentation

Supported Functions and Operators

The following functions and operators are supported by the Metadata Editor.

Supported Functions

The table below contains a listing of supported functions. Examples are shown below for each supported function.

Function Name Parameters Description
OR Two or more boolean expression parameters Returns true if one or more parameters are true
OR( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )
Function Name Parameters Description
AND Two or more boolean expression parameters Returns true if all parameters are true
AND( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )
Function Name Parameters Description
LIKE Two parameters Compares a column to a regular expression, using "%" as wild cards
LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%")
Function Name Parameters Description
IN Two or more parameters Checks to determine if the first parameter is in the following list of parameters
IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian
                Jones")
Function Name Parameters Description
NOW none The current date
NOW()
Function Name Parameters Description
DATE Three numeric parameters, year, month, and day A specified date
DATE(2008;4;15)
Function Name Parameters Description
DATEVALUE One text parameter "year-month-day" A specified date
DATEVALUE("2008-04-15")
Function Name Parameters Description
CASE Two or more parameters Evaluates the first, third, etc. parameter, and returns the second, fourth, etc parameter value if there are an odd number of parameters, the last parameter is returned if no other parameter evaluates to true. Note that when using this function, the formula must be set on a new column as shown below.
CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars"
                )
Function Name Parameters Description
COALESCE One or more parameters Returns the first non null parameter
CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars"
                )
Function Name Parameters Description
DATEMATH One or more parameters; DateMath Javadoc for full syntax

Returns a date based on an expression.

Note: This function does not return a timestamp irrespective of the implementation details in the description below.

DATEMATH("0:ME -1:DS") - 00:00:00.000 of the day before the last day of the current month
DATEMATH("0:MS  0:WE") - 23:59:59.999 the last day of the first week of the month
DATEMATH("0:ME") - 23:59:59.999 of the last day of the current month
DATEMATH("5:Y") -  the current month, day and time 5 years in the future
DATEMATH("5:YS") - 00:00:00.000 of the first day of the years 5 years in the future 

Supported Aggregate Functions

The table below contains a listing of supported aggregate functions.

Function Name Description
SUM Sums a specific columns values determined by grouping
COUNT Counts a specific columns values determined by grouping
AVG Averages a specific columns values determined by grouping
MIN Selects the minimum column value determined by grouping
MAX Selects the maximum column value determined by grouping

Supported Operators

The table below contains a listing of supported operators.

Operator Description
= Returns true if two expressions are equal
> Returns true if first expression is larger than the second
< Returns true if first expression is smaller than the second
>= Returns true if first expression is larger than or equal to the second
<= Returns true if first expression is smaller than or equal to the second
<> Returns true if two expressions are not equal
+ Adds two values
- Subtracts two values
* Multiplies two values
/ Divides two values