Skip to main content

Celonis Product Documentation

FAQ - Process Query Language

Description

This section answers the most common frequently asked questions about PQL.

General

Which characters are allowed in table or column names?

You can find all the information you need about table and column names here.

What do I do when I see this error: "Could not open swap file: <some file>"?

Try to do a "Force Complete Reload" in the data model.

What does it mean if an operator is marked as deprecated?

This means that there is a new operator available as a replacement. For more information check the Deprecated Operators section here.

Data type conversion

You can find detailed information about data type conversion here.

How do I cast from INT to FLOAT?

To convert an INT to a FLOAT, use *1.0

How do I cast from FLOAT to INT?

To convert a FLOAT to an INT, use the FLOOR or ROUND operators.

How do I cast from STRING to INT?

To convert a STRING to an INT, use the TO_INT operator.

Null handling

How do I change the result of a KPI from NULL to 0?

Use the COALESCE operator.

How do I catch an empty variable in a filter statement?

To avoid errors for empty variables, the variable syntax allows conditional statements. The following example only applies the filter statement if the variable is not empty:

<% if(NEW_VARIABLE != "") { %> FILTER "table"."column" LIKE '%<%=NEW_VARIABLE%>%'; <% } %>

KPI calculation

Why is KPI("Number of process variants") different from COUNT(DISTINCT VARIANT("_CEL_ACTIVITIES"."ACTIVITY" ))?

The predefined saved formula KPI("Number of process variants") uses the SHORTENED operator with the default max_cycle_length of 2.

Why do the results of KPI values in OLAP tables change depending on other KPIs?

In general, Celonis performs implicit joins when a query accesses several tables. The joins are executed according to the foreign key relationships defined in the data model. Therefore, the calculation of individual KPIs depends on the table for which the join is executed. You can find more information about the join functionality here.

How do I find out if the average value of a company is above or below the overall average value?

Use the GLOBAL operator to calculate the overall average value and compare it with the average value of a company:

CASE WHEN AVG ( "Companies"."Value" ) ) > GLOBAL ( AVG ( "Companies"."Value" ) ) THEN 'larger' ELSE 'smaller' END

Throughput time calculation

How do I calculate the maximum number of days between any two activities in the process for each case?

Use the PU_MAX operator in combination with the SOURCE / TARGET operator.

PU_MAX("_CEL_CASES",SECONDS_BETWEEN(TARGET("_CEL_ACTIVITIES"."EVENTTIME"),SOURCE("_CEL_ACTIVITIES"."EVENTTIME")))

You can find more information about PU functions and SOURCE/TARGET in our cheat sheets here.

Why does the throughput time in the Process Explorer and in a Single KPI component differ?

There are several possible reasons for this:

  • Rounding issues: The throughput time in the Process Explorer is calculated with the SOURCE / TARGET operator by mapping the timestamps via REMAP_TIMESTAMP to SECONDS. Then the result is converted to MINUTES/HOURS/ DAYS. If the throughput time calculation in the Single KPI is done by mapping the timestamps via REMAP_TIMESTAMP to MINUTES, this can cause differences in the two results.

  • Different ways of calculation: As mentioned in the previous point, the throughput time in the Process Explorer is calculated by using the SOURCE / TARGET operator. If the throughput time calculation in the Single KPI is done with another operator, this might cause differences in the results.

  • Calendar enabled in the analysis: It is possible to enable calendars for a whole analysis (in the Datamodel configuration), which will only take the configured working hours into consideration for the throughput time shown by the Process & Variant Explorer. For custom PQL queries, the calendar configuration has to be specified manually. For more information, see the documentation on calendars.

You can find detailed examples of calculations of throughput times here.

Why don't the throughput times in the Variant Explorer add up?

The variant explorer can show the throughput time in two different ways:

  • The median throughput time of all cases belonging to a variant, right next to its case coverage

  • The throughput time between each activity by switching the edge KPI to throughput time

You might expect that adding all the throughput times between the activities will lead to the same number as shown right next to the case coverage. This is not always the case. A possible deviation is caused by adding median results. The throughput time is calculated by taking the median. In general, median is more robust against outliers but there is no guarantee that the sum of medians of subgroups returns the same result as the median over the whole group. You can find detailed examples on the calculation of throughput times here.

Conformance calculation

How do I use the results of the Conformance checker in other parts of the analysis, for example in an OLAP table?

Store the conformance query in a variable. Then you can calculate the:

  • Overall conformance: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN 1 ELSE 0 END)

  • Conforming Throughput time: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN CALC_THROUGHPUT(CASE_START TO CASE_END, REMAP_TIMESTAMPS("_CEL_ACTIVITIES"."EVENTTIME", HOURS)) / 24 ELSE NULL END)

  • Conforming steps per case: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN PU_COUNT("_CEL_CASES", "_CEL_ACTIVITIES"."ACTIVITY") ELSE NULL END)