Skip to main content
Pentaho Documentation

Supported functions and operators

Parent article

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 NameParametersDescription
ORTwo or more boolean expression parametersReturns true if one or more parameters are true.

OR( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )

Function NameParametersDescription
ANDTwo or more boolean expression parametersReturns true if all parameters are true.

AND( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )

Function NameParametersDescription
LIKETwo parametersCompares a column to a regular expression, using % as a wild card.

LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%")

Function NameParametersDescription
INTwo or more parametersChecks to determine if the first parameter is in the following list of parameters.

IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian
                Jones")

Function NameParametersDescription
NOWNoneThe current date

NOW()

Function NameParametersDescription
DATEThree numeric parameters: year, month, and dayA specified date

DATE(2008;4;15)

Function NameParametersDescription
DATEVALUEOne text parameter "year-month-day"A specified date

DATEVALUE("2008-04-15")

Function NameParametersDescription
CASETwo or more parametersEvaluates 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 NameParametersDescription
COALESCEOne or more parametersReturns 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 NameParametersDescription
DATEMATHOne or more parameters; see DateMath Javadoc for full syntax

Returns a date based on an expression.

Note that 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 NameDescription
SUMSums a specific columns values determined by grouping.
COUNTCounts a specific columns values determined by grouping.
AVGAverages a specific columns values determined by grouping.
MINSelects the minimum column value determined by grouping.
MAXSelects the maximum column value determined by grouping.

Supported operators

The table below contains a listing of supported operators.

OperatorDescription
=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.