Hitachi Vantara Pentaho Community Wiki
Child pages
  • 02. Pentaho Metadata Formulas

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel

Please see the Pentaho InfoCenter for the latest version of this document: http://infocenter.pentaho.com/help/topic/pme_user_guide/concept_pme_metadata_formulas.html

Formula Overview

Formulas have multiple uses in Pentaho Metadata. 

...

Function Name

Parameters

Description

Example

OR

two or more boolean expression parameters

Returns true if one or more parameters are true

OR(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="78163bd0f80ea075-a1296aeb-494a4642-a9d78eff-19b7913b907b3c8ec095d319"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="b8ea00406d936ddb-089e25c4-4f864bdf-8d13b81e-ee344102f78a54b898ba4d4e"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000
]]></ac:plain-text-body></ac:structured-macro>
)

AND

two or more boolean expression parameters

Returns true if all parameters are true

AND(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8688f3fd2f32a0a4-2b89f187-40e5425c-93fabe64-74a59cce0b298d593a0c7b21"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="2fb915d29806e2fa-86c2a740-4a0344ea-858c80f6-33ad30c17089dc90dc0d0be6"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000
]]></ac:plain-text-body></ac:structured-macro>
)

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="1e148ec2121022f7-4230dd41-4cc64e87-9d9c8cb9-b3c826b695c65a49d88eb4b3"><ac:plain-text-body><![CDATA[

LIKE

two parameters

Compares a column to a regular expression, using "%" as wild cards

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

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="b7d9ffb333d8e33e-474c0d0b-46b147a8-b2e6889a-2713efd2aa2c0c87357fce13"><ac:plain-text-body><![CDATA[

CONTAINS

two parameters

Determines if a column contains a string.

CONTAINS([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="b6cd08f5fd5e7ba9-526b371c-46b94186-b7b2b626-b294719af9f7409f5d724b8b"><ac:plain-text-body><![CDATA[

BEGINSWITH

two parameters

Determines if a column begins with a string.

BEGINSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "JOE")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9dce4b94553e54a6-ba8104d0-4ee9475c-8daf81b8-eaa5cfca88273206b344d7b0"><ac:plain-text-body><![CDATA[

ENDSWITH

two parameters

Determines if a column ends with a string.

ENDSWITH([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "SMITH")

]]></ac:plain-text-body></ac:structured-macro>

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9ba01decf3023686-94fdeaf7-4a2b406a-8740acd7-5fc4ecb612f6a2d0f76b1c74"><ac:plain-text-body><![CDATA[

IN

two or more parameters

Checks to see if the first parameter is in the following list of parameters

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

]]></ac:plain-text-body></ac:structured-macro>

NOW

none

The current date

NOW()

DATE

three numeric parameters, year, month, and day

A specified date

DATE(2008;4;15)

DATEVALUE

one text parameter "year-month-day"

A specified date

DATEVALUE("2008-04-15")

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 needs to be set on a new column, not on the BT_CUSTOMER.BC_CUSTOMER_CUSTOMERNAME (using the example to the right)

CASE(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9fe1aed7b4c9e8eb-c043eaaf-43064373-bb10bd13-33ef759a946d338631d9642c"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
]]></ac:plain-text-body></ac:structured-macro>
"European Cars";
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="1104ed0e3b7465ea-0338abb0-489c4866-b99ca548-3c94c82f364f1de7aad52a49"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars";
]]></ac:plain-text-body></ac:structured-macro>
"Asian Cars";
"Unknown Cars"
)

COALESCE

one or more parameters

returns the first non null parameter

COALESCE(
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="0b87a19d3794568a-ced798b3-448e4b91-bed88179-ec975aae04718c042b9dbf7f"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME];
]]></ac:plain-text-body></ac:structured-macro>
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="f8af5319634e8126-b1e1c52f-46024b96-86e084c3-3a4ebd589fa04eb72f3da802"><ac:plain-text-body><![CDATA[[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID];
]]></ac:plain-text-body></ac:structured-macro>
"Customer is Null"
)

DATEMATH

one expression parameter

returns a date based on an expression. DateMath Javadoc for full syntax

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

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="04578e37f47c9097-ef0f879e-416d4831-bd11aaec-f3d72434a9ee3380fc4b3b48"><ac:plain-text-body><![CDATA[

ISNA

one parameter

returns true if the value is null

ISNA([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID])

]]></ac:plain-text-body></ac:structured-macro>

NULL

none

returns the null value

NULL()

TRUE

none

returns true

TRUE()

FALSE

none

returns false

FALSE()

...