Skip to main content

Celonis Product Documentation

DOMAIN_TABLE
Description

The DOMAIN_TABLE function can be used to create a temporary table from various column(s), which can be used as a target table inside all PU-functions.

It is not possible to use the same table as the source and the target table in a PU-function. However, the DOMAIN_TABLE function can be used to create a temporary table from various column(s). These column(s) must have a common table to which the columns are joined to. The source column for the PU-function has to be from a common table of the specified column(s) for the temporary table. Further documentation about join relationships can be found in Join functionality.

The temporary table created by DOMAIN_TABLE contains all unique combinations of values from the specified column(s) existing in the result of joining these column(s) to their closest common table. The resulting temporary table always has a 1:N relationship with that closest common table of the specified column(s).

In the following figure this concept is being visualized using the query:

  PU_SUM(DOMAIN_TABLE("Activities"."activity",ROUND_DAY("Activities"."timestamp")), "Activities"."values"))
 

For this, the DOMAIN_TABLE of all days and activities in the input table is created, and a grouping of equal activities on the same day for each distinct day and activity is made. The sum of the corresponding values is calculated with PU_SUM.

domain_table.png
Syntax
 DOMAIN_TABLE ( table.column, ... )
Use Cases
Examples

[1]

For each case ID, calculate the number of times the associated company code is contained in the table using DOMAIN_TABLE:

Query

Column1

         "caseTable"."caseId"
        

Column2

         PU_COUNT ( DOMAIN_TABLE ( "caseTable"."companyCode" ) , "caseTable"."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

Result

Column1 : int

Column2 : int

1

3

2

3

3

3

4

2

5

2

6

1

[2]

Filter on all cases where the associated company code occurs less than three times in the table:

Query

Filter

         FILTER PU_COUNT ( DOMAIN_TABLE ( "caseTable"."companyCode" ) , "caseTable"."companyCode" ) < 3;
        

Column1

         "caseTable"."caseId"
        

Column2

         PU_COUNT ( DOMAIN_TABLE ( "caseTable"."companyCode" ) , "caseTable"."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

Result

Column1 : int

Column2 : int

4

2

5

2

6

1

[3]

In this example, the activity table is filtered on all entries that happened on a day with more than three actvities. For this, the DOMAIN_TABLE of all days in the input table is created, and the number of activities for each distinct day is calculated with PU_COUNT. The PU_COUNT result is then compared to '3' in the FILTER query:

Query

Filter

         FILTER PU_COUNT ( DOMAIN_TABLE ( ROUND_DAY ( "Activities"."timestamp" ) ) , "Activities"."activity" ) > 3;
        

Column1

         "Activities"."case"
        

Column2

         "Activities"."activity"
        

Column3

         "Activities"."timestamp"
        

Input

Output

Activities

case : int

activity : string

timestamp : date

1

'A'

Wed Jan 01 2020 01:00:00.000

1

'B'

Thu Jan 02 2020 02:00:00.000

1

'C'

Fri Jan 03 2020 03:00:00.000

2

'A'

Wed Jan 01 2020 01:00:00.000

2

'B'

Thu Jan 02 2020 02:00:00.000

3

'C'

Thu Jan 02 2020 03:00:00.000

3

'D'

Thu Jan 02 2020 04:00:00.000

Result

Column1 : int

Column2 : string

Column3 : date

1

'B'

Thu Jan 02 2020 02:00:00.000

2

'B'

Thu Jan 02 2020 02:00:00.000

3

'C'

Thu Jan 02 2020 03:00:00.000

3

'D'

Thu Jan 02 2020 04:00:00.000

[4]

This example is related to the figure in the overview. For this, the DOMAIN_TABLE of all days and activities in the input table is created, and the number of equal activities on the same day for each distinct day and activity is calculated with PU_SUM. The PU_SUM result is then outputed to join partner in the input table.

Query

Column1

         "Activities"."activity"
        

Column2

         ROUND_DAY ( "Activities"."timestamp" )
        

Column3

         PU_SUM ( DOMAIN_TABLE ( "Activities"."activity" , ROUND_DAY ( "Activities"."timestamp" ) ) , "Activities"."values" )
        

Input

Output

Activities

case : int

activity : string

timestamp : date

values : int

1

'A'

Wed Jan 01 2020 01:00:00.000

1

1

'B'

Thu Jan 02 2020 02:00:00.000

3

1

'C'

Fri Jan 03 2020 03:00:00.000

6

2

'A'

Wed Jan 01 2020 01:00:00.000

10

2

'B'

Thu Jan 02 2020 02:00:00.000

15

3

'C'

Thu Jan 02 2020 03:00:00.000

21

3

'D'

Thu Jan 02 2020 04:00:00.000

28

Result

Column1 : string

Column2 : date

Column3 : int

'A'

Wed Jan 01 2020 00:00:00.000

11

'B'

Thu Jan 02 2020 00:00:00.000

18

'C'

Fri Jan 03 2020 00:00:00.000

6

'A'

Wed Jan 01 2020 00:00:00.000

11

'B'

Thu Jan 02 2020 00:00:00.000

18

'C'

Thu Jan 02 2020 00:00:00.000

21

'D'

Thu Jan 02 2020 00:00:00.000

28

See also: