Concept: Prism Expression Language
Prism Analytics includes its own expression language that's composed of functions and operators. You use the Prism expression language to create an
expression
, which you can use to create new values or filter existing values.An expression computes or produces a value by combining fields, constant values, operators, and functions. An expression outputs a value of a particular field type, such as Numeric or Text values. Simple expressions can be a single constant value, the values of a given field, or just a function. You can use operators to join two or more simple expressions into a complex expression.
You can use expressions in datasets:
- Calculated fields.Use expressions to define calculated fieldsthat operate on the source data. A calculated field expression generates its values based on a calculation or condition, and returns a value for each input row. Calculated field expressions can contain values from other fields, constants, mathematical operators, comparison operators, or built-in row functions.
- Filter stages.Use an expression in a Filter stage to limit the scope of the source data of a dataset.
The expression builder helps you create calculated field expressions in a dataset. It displays the available fields in the dataset, plus the list of the Prism functions. It validates your expressions for correct syntax, input field types, and so on.
Function Inputs and Outputs
Functions take one or more input values and return an output value. Input values can be a literal value or the name of a field that contains a value. In both cases, the function expects the input value to be a particular field type such as Text or Integer. Example: The CONCAT() function combines Text inputs and outputs a new Text.
This example demonstrates how to use the CONCAT() function to concatenate the values in the
month
, day
, and year
fields separated by the literal forward slash character:CONCAT([month],"/",[day],"/",[year])
A function return value might be the same as its input type or it might be an entirely new field type. Example: The TO_DATE() function takes a Text as input, but outputs a Date value. If a function expects a Text, but is passed another field type as input, the function returns an error.
Typically, functions are classified by what field type they take or what purpose they serve. Example: CONCAT() is a text function and TO_DATE() is a field type conversion function.
Nesting Functions
Functions can take other functions as arguments. Example: You can use the CONCAT function as an argument to the TO_DATE() function. The final result is a Date value in the format 10/31/2014.
TO_DATE(CONCAT([month],"/",[day],"/",[year]),"MM/dd/yyyy")
The nested function must return the correct field type. So, because TO_DATE() expects text input and CONCAT() returns a text, the nesting succeeds.
Referring to Fields in the Current Dataset
Workday recommends enclosing all field names with square brackes (
[]
). Example:TO_INT([sales])
TO_INT([Sale Amount])
TO_INT([2013_data])
TO_INT([count])
If a field name contains a ] (closing square bracket), you must escape the closing square bracket by doubling it ]]. Suppose you have this field name:
[Total Sales]
You enclose the entire field name in square brackets and escape the closing bracket that is part of the actual field name:
[[Total Sales]]]
Literal Text Values
To specify a literal or actual Text value, enclose the value in double quotes (
"
). Example: This expression converts the values of a gender
field to the literal values of male
, female
, or unknown
:
CASE WHEN [gender]="M"THEN"male"WHEN [gender]="F"THEN"female"ELSE"unknown"END
To escape a literal quote within a literal value itself, double the literal quote character. Example:
CASE WHEN [height]="60""" THEN "5 feet" WHEN [height]="72""" THEN "6 feet" ELSE "other" END
The REGEX() function is a special case. In the REGEX() function, Text expressions are also enclosed in quotes. When a Text expression contains literal quotes, double the literal quote character. Example:
REGEX([height], "\d\'(\d)+""")
Literal Date Values
To refer to a Date value in a Filter stage expression, you must use this format (or any shortened version of it) without any enclosing quotation marks or other punctuation:
yyyy-MM-ddTHH:mm:ss:SSSZ
Example:
[Order Date] BETWEEN 2016-06-01T00:00:00.000Z AND 2016-07-31T00:00:00.000Z
If the Filter expression is a shortened version of the full format, then Prism assigns any values that aren't included a value of zero (0). Example: This expression:
[Order Date] >= 2017-01-01
Is equivalent to:
[Order Date] >= 2017-01-01T00:00:00.000Z
To refer to a literal date value in a calculated field expression, you must specify the format of the date and time components using TO_DATE, which takes a Text literal argument and a format string. Example:
CASE WHEN [Order Date]=TO_DATE("2013-01-01 00:00:59 PST","yyyy-MM-dd HH:mm:ss z") THEN "free shipping" ELSE "standard shipping" DONE
Literal Numeric Values
For literal numeric values, you can just specify the number itself without any special escaping or formatting. Example:
CASE WHEN [is married]=1THEN "married" WHEN [is married]=0THEN "not_married" ELSE NULL END