Filter Expressions

Operators

Intelligence API supports a subset of OData filter operators. Note that all operators must be written in lowercase to comply with OData syntax requirements.

Comparison operators

Operator Description Examples
eq Equal City eq 'Redmond'
City eq 'St. John''s'
ne Not equal City ne 'London'
City ne 'King''s Cross'
gt Greater than Price gt 20
ge Greater than or equal Price ge 10
lt Less than Price lt 20
le Less than or equal Price le 100
in Is a member of City in ('Redmond', 'London')

Logical operators

Operator Description Examples
and Logical and Price le 200 and Price gt 3.5
or Logical or Price le 3.5 or Price gt 200
not Logical negation not endswith(Description,'milk')

Grouping operators

Operator Description Examples
() Precedence grouping (Price eq 5) or (City eq 'London')

String functions

Function Examples
contains contains(CompanyName,'freds')
contains(LastName,'O''Neil')
endswith endswith(CompanyName,'Futterkiste')
startswith startswith(CompanyName,'Alfr')

Escaping single quotes in string literals

Please note that single quotes within string literals must be represented as two consecutive single quotes.

Example:

O'Neil is represented as O''Neil.

For more information, see the OData syntax rules here.

Filter Expressions and Types

The filter expressions will be sensitive to the expressions field’s types. Below is a table outlining the supported types and examples of appropriate filter expressions.

Celonis Data Type Celonis Data Format Filter Example Filter results (mapping to PQL)
String Standard (No format) 
Name eq 'John Doe'
 
“TABLE”.“Name” = ‘John Doe’
Boolean True/False 
Active eq true
 
“TABLE”.“Active”” = 0
Yes/No
0/1
Integer Standard (No format) 
Age eq 10
 
“TABLE”.“Age” = 10
Rounded (,.0f)
Percentage (%)
Decimal Number (#.##)
Abbreviated (##.#k)
Float Standard (No format) 
Ratio eq 10.2
 
“TABLE”.“Ratio” = 10.2
Rounded (,.0f)
Percentage (%)
Decimal Number (#.##)
Abbreviated (##.#k)
Date Standard (No format) 
Date eq 2015-05-07
 
TO_STRING(“TABLE”.“Date”, FORMAT (‘%Y-%m-%d’)) = ‘2015-05-07’
Date (Y-M-D)
Date and Time (Y-M-D H:M)
Time or DateTime or Timestamp Standard (No format) 
Date eq 2023-12-05T15:48:44Z
Date eq 2023-12-05T15:48Z
Date eq 2023-12-05T15:48+03:00
 
TO_STRING(“TABLE”.“Date”, FORMAT (‘%Y-%m-%d %H:%M:%S’)) = ‘2015-05-07 15:48:44’
TO_STRING(“TABLE”.“Date”, FORMAT (‘%Y-%m-%d %H:%M’)) = ‘2015-05-07 15:48’
Date (Y-M-D)
Date and Time (Y-M-D H:M)
Time (H:M)

Using the filterExpr parameter

To filter data of a single record of a given Knowledge Model

To filter the data of a specific Knowledge Model Record, you can use the Intelligence API's Getting Record Data endpoint. This is useful when you want to retrieve a subset of the record's data based on certain conditions. The endpoint uses the filterExpr parameter to apply filters. Here's how it works:

Single filterExpr parameter

You can combine multiple filter conditions into a single filterExpr parameter using the and operator.
Example: filterExpr=active eq true and name eq ‘John Doe’

Multiple filterExpr parameter

The API also supports providing multiple filterExpr parameters. The API automatically combines these with the and operator, achieving the same result as the single-parameter method.
Example: filterExpr=active eq true&filterExpr=name eq ‘John Doe’

Important Notes

  • The field identifier used in your filter expression is the attribute ID from Celonis Studio.
  • Attribute IDs are case-sensitive . Be sure to match the casing exactly as it appears in Celonis Studio.

To filter data of any record and/or KPI of a given Knowledge Model

To filter the data from different records of a Knowledge Model and combine them with KPIs (Key Performance Indicators), you can use the Intelligence API's Getting Knowledge Model Data endpoint.
It uses the filterExpr parameter to introduce filter expressions, providing greater flexibility when querying different data within the Knowledge Model. To achieve this, you must specify both the record and the field to filter, allowing you to combine fields from different records within the same Knowledge Model. Here's how it works:

Single filterExpr parameter

You can combine multiple filter conditions into a single filterExpr parameter using the and operator.
Example: filterExpr=activities.name eq ‘Release Order’ and users.active eq true

Multiple filterExpr parameter

The API also supports providing multiple filterExpr parameters. The API automatically combines these with the and operator, achieving the same result as the single-parameter method.
Example: filterExpr=activities.name eq ‘Release Order’&filterExpr=users.active eq true

Important Notes

  • The field identifier used in your filter expression is the record ID and attribute ID from Celonis Studio.
  • Record IDs and Attribute IDs are case-sensitive . Be sure to match the casing exactly as they appear in Celonis Studio.