Supported functions for object-centric transformations
Not all functions are enabled for Celonis transformations. The functions and operators on this list are enabled for use in custom transformations for object-centric process mining. At the end of this topic, there's advice on how to replace functions you can't find on this list with suitable alternatives that are supported for object-centric process mining.
The list of enabled functions for case-centric transformations is different. You can find it at Supported functions for case-centric transformations.
Aggregate operators
AVG
COUNT
FIRST_VALUE (with RESPECT NULLS | IGNORE NULLS)
LAST_VALUE (with RESPECT NULLS | IGNORE NULLS)
LAG
LEAD
MIN
MAX
SUM
Date part functions
YEAR
QUARTER
MONTH
WEEK
DAYOFYEAR
DAYOFMONTH
DAYOFWEEK
HOUR
MINUTE
SECOND
Window rank functions
DENSE_RANK
RANK
ROW_NUMBER
Scalar functions
ABS
CAST
COALESCE (fields must have the same data type)
CONCAT
DATEDIFF
DATE_TRUNC
EXTRACT
LEFT
LENGTH
LOWER
LPAD
LTRIM
NOW
NULLIF
POWER
RANDOM
REPLACE
RIGHT
SPLIT_PART
SUBSTRING
TIMESTAMPADD
TIMESTAMPDIFF
TRIM
UPPER
Replacing functions no longer supported for object-centric data models
Here's how to replace functions that were supported with Vertica SQL and case-centric transformations, with suitable alternatives.
Vertica SQL function | Replacement |
---|---|
|
The fields must have the same data type. |
|
|
| Import the column to be aggregated into a dedicated object type connected to the perspective, then use |
|
Vertica allows you to specify a literal with the TIMESTAMP type using |
|
|
|
|
Implicit casting |
Explicitly cast the data types. |
Implicit data type conversion for IDs from INT to STRING |
All IDs or primary keys for objects and events must be a string, and you need to do the conversion explicitly. |
|
|
|
Your epoch timestamp (here it's a ten-digit timestamp 1706692473) must be an integer and have the right number of digits to convert to your selected datetime format. Alternatively, you may use
|
|
|
|
Or use |
| Use the CELONIS_CALENDAR table, for example: SELECT CAST("Date" AS TIMESTAMP) FROM "CELONIS_CALENDAR" WHERE "Date" >='2017-01-01' AND DATE <='2017-01-05' ORDER BY "Date" |
Using table and column names such as TIME, CAST, SELECT without quotation marks |
All table and column names that match an SQL keyword need to be enclosed with proper quotation marks. |
End a script with a semi-colon (;) | Do not end a script with a semi-colon (;) |