Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CONSTANT

Applies to: CELONIS 4.7

Description

To aggregate all values from a column of a parent table to a single value, the CONSTANT function can be used as a child table inside a PU function. The resulting value is a constant.

Syntax
CONSTANT()
Examples

[1] Calculate the average of the case table values using CONSTANT.

Query

Column1

PU_AVG ( CONSTANT ( ) , "caseTable"."value" )

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

6

'003'

200

Result

Column1 : FLOAT

333.3333333333333

[2] Calculate the average of the case table values using CONSTANT. In contrast to the regular AVG function, and like in all PU functions, FILTERs are ignored.

Query

Filter

FILTER "caseTable"."caseId" = 1;

Column1

PU_AVG ( CONSTANT ( ) , "caseTable"."value" )

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

6

'003'

200

Result

Column1 : FLOAT

333.3333333333333

[3] In this example, only company codes with an average value larger than the overall average value are returned. The overall average value is calculated using CONSTANT inside a PU_AVG. The result of this behaves like a constant value, such that it can be compared with the result of the first PU_AVG which calculates the average value per companyDetail.

Query

Filter

FILTER
    PU_AVG ( "companyDetail" , "caseTable"."value" ) > PU_AVG ( CONSTANT ( ) , "caseTable"."value" );

Column1

"companyDetail"."companyCode"

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

6

'003'

200

companyDetail

companyCode : STRING

country : STRING

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : STRING

'001'