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).

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

See also: