Skip to main content

Celonis Product Documentation

UNIQUE_ID
Description

The UNIQUE_ID function returns a unique non-negative INT for each unique tuple in the combination of the input columns.

There is no guarantee which INT will be assigned to which tuple. However, it is guaranteed that the result is always non-negative.

SYNTAX
 UNIQUE_ID ( table.column1 , ... , table.columnN )
NULL handling

This operator does not treat NULL values specially. Additionally, the result of UNIQUE_ID can never be NULL.

Examples

[1]

UNIQUE_ID can be used to count the distinct number of unique combinations of values from different columns. This example shows a list of six vendors together with the city and country of their headquarters. We want to count the number of different cities our vendors come from. Since just the city names are not unique, it is required to count unique combinations of city name and country. We can use UNIQUE_ID to give a unique ID to each of those combinations, and apply COUNT DISTINCT on these unique IDs:

Query

Column1

         COUNT ( DISTINCT UNIQUE_ID ( "Vendors"."City" , "Vendors"."Country" ) )
        

Input

Output

Vendors

Name : string

City : string

Country : string

'A'

'Munich'

'DE'

'B'

'Berlin'

'DE'

'C'

'Berlin'

'NH'

'D'

'Berlin'

'DE'

'E'

'Bristol'

'UK'

'F'

'Bristol'

'TN'

Result

Column1 : int

5

[2]

Same example as above, but the COUNT DISTINCT is not applied. We can see the exact result of the UNIQUE_ID function per vendor. The number that gets assigned to each unique tuple of city and country does not follow a particular pattern, but it is guaranteed that it is non-negative:

Query

Column1

         "Vendors"."Name"
        

Column2

         UNIQUE_ID ( "Vendors"."City" , "Vendors"."Country" )
        

Input

Output

Vendors

Name : string

City : string

Country : string

'A'

'Munich'

'DE'

'B'

'Berlin'

'DE'

'C'

'Berlin'

'NH'

'D'

'Berlin'

'DE'

'E'

'Bristol'

'UK'

'F'

'Bristol'

'TN'

Result

Column1 : string

Column2 : int

'A'

4

'B'

0

'C'

1

'D'

0

'E'

3

'F'

2