Skip to main content

Celonis Product Documentation

Static PQL Functions

Description

Static PQL functions provide a lightweight way to check general properties of a data model or expression before actually executing a PQL query. For example, you can check the data type of a data model column and based on the type modify the actual PQL query to be executed.

The following static PQL functions are available:

  • ARGUMENT_COUNT counts the number of arguments passed to it.

  • COLUMN_TYPE returns the data type of a data model column.

  • IF evaluates a list of static conditions and replaces itself with the query for the first static condition that is true.

  • STATIC CASE WHEN evaluates a list of static conditions and replaces itself with the expression for the first static condition that is true.

Example - Preventing empty IN statements

Suppose you want to sum the total order amount for several countries (per country). The countries are selected from a drop-down menu and passed to the query as an array variable. If no country is selected, we want the total order amount for all countries. We can use the static PQL functions IF and ARGUMENT_COUNT to achieve this:

[1]

Suppose the user selected the country 'DE'.

Query

KPI "selected_country"

         {p1}
        

If

         IF ARGUMENT_COUNT ( 'DE' ) > 0 THEN FILTER "Orders"."Country" IN ( KPI ( "selected_country" , 'DE' ) ) ; END;
        

Column1

         "Orders"."Country"
        

Column2

         SUM ( "Orders"."Amount" )
        

Input

Output

Orders

Country : string

Amount : int

'DE'

10

'US'

15

'US'

5

'DE'

10

'DE'

5

Result

Column1 : string

Column2 : int

'DE'

25

[2]

Suppose the user did not select any country.

Query

KPI "selected_country"

         {p1}
        

If

         IF ARGUMENT_COUNT ( ) > 0 THEN FILTER "Orders"."Country" IN ( KPI ( "selected_country" , 'DE' ) ) ; END;
        

Column1

         "Orders"."Country"
        

Column2

         SUM ( "Orders"."Amount" )
        

Input

Output

Orders

Country : string

Amount : int

'DE'

10

'US'

15

'US'

5

'DE'

10

'DE'

5

Result

Column1 : string

Column2 : int

'DE'

25

'US'

20

This is how the query would actually look like with an Analysis frontend variable:

  IF ARGUMENT_COUNT ( <%=COUNTRIES%> ) > 0 THEN FILTER "Orders"."Country" IN ( <%=COUNTRIES%> ) ; END ;
 
Example - Converting an unknown type

[3]

TO_STRING does not support FLOAT and STRING columns as inputs, and it requires an additional format argument for DATE inputs. With STATIC CASE WHEN and COLUMN_TYPE, we can write a string conversion query that is independent of the data type of a column.

Query

Column1

         STATIC CASE WHEN COLUMN_TYPE ( "Table"."Column" ) = 'INT' THEN TO_STRING ( "Table"."Column" ) WHEN COLUMN_TYPE ( "Table"."Column" ) = 'DATE' THEN TO_STRING ( "Table"."Column" , FORMAT ( '%Y-%m-%d' ) ) WHEN COLUMN_TYPE ( "Table"."Column" ) = 'FLOAT' THEN CONCAT ( '' , "Table"."Column" ) ELSE "Table"."Column" END
        

Input

Output

Table

Column : int

1

2

3

Result

Column1 : string

'1'

'2'

'3'