Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

INDEX_ORDER

Applies to: CELONIS 4.6

Description

INDEX_ORDER returns a column with INTEGER indices, starting from 1. The indices indicate the order of the rows, if the input table values had been sorted.

The INDEX_ORDER function creates an INTEGER index column. The index column contains a unique INTEGER value for every row of a required reference input column. The values of the indices are based on the order of the values in the input column.

INDEX_ORDER can be applied to INT, FLOAT, DATE or STRING columns. The result is a column of type INTEGER.

Syntax
INDEX_ORDER( column [, sorting ] [, GROUP( group_column, ... ) ] )

  • column: The source column on which the index column will be based.

  • sorting: An optional sorting of the indices, which defaults to ascending.

    • ASC: Ascending index order values.

    • DESC: Descending index order values.

  • grouping: Optional grouping columns. Independent index order ranges will be created for each group, all starting from 1.

Ordering

Ordering can be ascending (default) or descending. Indices are counted from 1. Equal input values are always given distinct output indices. The indices for a given range of equal input values are incremented starting from the topmost value of this range of the input column:

[1] Create an index order for an input column that contains equal values.

Query

Column1

INDEX_ORDER ( "Table1"."Column1")

Input

Output

Table1

Column1 : INT

1

1

2

2

2

Result

Column1 : INT

1

2

3

4

5

The above behavior also holds for descending ordering of indices.

[2] Create a descending index order for an input column that contains equal values.

Query

Column1

INDEX_ORDER ( "Table1"."Column1", DESC)

Input

Output

Table1

Column1 : INT

1

1

2

2

2

Result

Column1 : INT

4

5

1

2

3

Null handling

Null values are ignored, meaning that the null value rows stay null in the result column and will count as an index order value.

[3] Create an index order for an integer input column that includes NULL values.

Query

Column1

INDEX_ORDER ( "Table1"."Column1")

Input

Output

Table1

Column1 : INT

11

null

14

null

15

Result

Column1 : INT

1

null

2

null

3

Examples

[4] Create an index order for an integer input column.

Query

Column1

INDEX_ORDER ( "Table1"."Column1")

Input

Output

Table1

Column1 : INT

11

13

14

12

15

Result

Column1 : INT

1

3

4

2

5

[5] Create an index order for a date input column.

Query

Column1

INDEX_ORDER ( "Table1"."Date")

Input

Output

Table1

Date : DATE

Tue Jan 01 2019 00:12:30.000

Tue Jan 01 2019 00:12:40.000

Tue Jan 01 2019 00:12:00.000

Tue Jan 01 2019 00:11:30.000

Tue Jan 01 2019 00:18:00.000

Tue Jan 01 2019 00:19:00.000

Output

Column1 : INT

3

4

2

1

5

6

[6] Create an index order for an input column in ascending order.

Query

Column1

INDEX_ORDER ( "Table1"."Column1", ASC)

Input

Output

Table1

Column1 : INT

11

13

14

12

15

Result

Column1 : INT

1

3

4

2

5

[7] Create an index order with a joined case table.

Query

Column1

"companyDetail"."country"

Column2

INDEX_ORDER("caseTable"."value")

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'002'

400

3

'003'

200

companyDetail

companyCode : STRING

country : STRING

'001'

'DE'

'002'

'FR'

'003'

'US'

Foreign Keys

companyDetail.companyCode

caseTable.companyCode

Result

Column1 : STRING

Column2 : INT

'DE'

3

'FR'

2

'US'

1

[8] Create an index order on an aggregation function.

Query

Column1

"companyDetail"."country"

Column2

INDEX_ORDER(COUNT_TABLE("caseTable"))

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'002'

400

3

'003'

200

companyDetail

companyCode : STRING

country : STRING

'001'

'DE'

'002'

'FR'

'003'

'US'

Foreign Keys

companyDetail.companyCode

caseTable.companyCode

Result

Column1 : STRING

Column2 : INT

'DE'

1

'FR'

2

'US'

3

[9] Create an index order for a date input column with equal values.

Query

Column1

INDEX_ORDER ( "Table1"."Date")

Input

Output

Table1

Date : DATE

Tue Jan 01 2019 00:12:30.000

Tue Jan 01 2019 00:12:30.000

Tue Jan 01 2019 00:12:30.000

Tue Jan 01 2019 00:11:30.000

Tue Jan 01 2019 00:18:00.000

Tue Jan 01 2019 00:12:30.000

Result

Column1 : INT

2

3

4

1

6

5

[10] Create an index order for a string input column with a function.

Query

Column1

INDEX_ORDER ( RIGHT("Table1"."Column1", 1) )

Input

Output

Table1

Column1 : STRING

'AO'

'BP'

'CQ'

'DR'

'ES'

Result

Column1 : INT

1

2

3

4

5

[11] Create an descending index order for an integer input column that includes NULL values.

Query

Column1

INDEX_ORDER ( "Table1"."Column1", DESC)

Input

Output

Table1

Column1 : INT

11

null

14

null

15

Result

Column1 : INT

3

null

2

null

1

[12] Create an index order for each group.

Query

Column1

"countryDetail"."country"

Column2

INDEX_ORDER("countryDetail"."value", GROUP("countryDetail"."country") )

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'002'

400

3

'003'

200

countryDetail

value : INT

country : STRING

city : STRING

100

'DE'

'Munich'

300

'FR'

'Amiens'

300

'US'

'Tucson'

200

'DE'

'Munich'

200

'FR'

'Paris'

100

'US'

'Seattle'

300

'DE'

'Hildesheim'

100

'FR'

'Amiens'

200

'US'

'Tucson'

Foreign Keys

countryDetail.value

caseTable.value

Result

Column1 : STRING

Column2 : INT

'DE'

1

'FR'

3

'US'

3

'DE'

2

'FR'

2

'US'

1

'DE'

3

'FR'

1

'US'

2

[13] Create an index order for each group, as given by more columns.

Query

Column1

"countryDetail"."country"

Column2

"countryDetail"."city"

Column3

INDEX_ORDER("countryDetail"."value", GROUP("countryDetail"."country", "countryDetail"."city") )

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'002'

400

3

'003'

200

companyDetail

value : INT

country : STRING

city : STRING

100

'DE'

'Munich'

300

'FR'

'Amiens'

300

'US'

'Tucson'

200

'DE'

'Munich'

200

'FR'

'Paris'

100

'US'

'Seattle'

300

'DE'

'Hildesheim'

100

'FR'

'Amiens'

200

'US'

'Tucson'

Foreign Keys

countryDetail.value

caseTable.value

Result

Column1 : STRING

Column2 : STRING

Column3 : INT

'DE'

'Munich'

1

'FR'

'Amiens'

2

'US'

'Tucson'

2

'DE'

'Munich'

2

'FR'

'Paris'

1

'US'

'Seattle'

1

'DE'

'Hildesheim'

1

'FR'

'Amiens'

1

'US'

'Tucson'

1

Advanced Example: Case Coverage

The INDEX_ORDER function allows to calculate KPIs, that require sorting. One example is calculating how many cases are covered by the N most common process variants.

[14] Given two cities, we want to find out how many cases are covered by the two most common variants for each city. Let us first look at the process variants.

Query

Column1

"Cases"."CITY"

Column2

VARIANT("Activities"."ACTIVITY")

Column3

COUNT("Cases"."CASE_ID")

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Fri Feb 01 2019 00:00:00.000

1

'B'

Sat Feb 02 2019 00:00:00.000

1

'C'

Sun Feb 03 2019 00:00:00.000

2

'A'

Fri Mar 01 2019 00:00:00.000

2

'B'

Sat Mar 02 2019 00:00:00.000

2

'C'

Sun Mar 03 2019 00:00:00.000

3

'A'

Mon Apr 01 2019 00:00:00.000

3

'B'

Tue Apr 02 2019 00:00:00.000

3

'C'

Wed Apr 03 2019 00:00:00.000

4

'A'

Wed May 01 2019 00:00:00.000

4

'B'

Thu May 02 2019 00:00:00.000

4

'C'

Fri May 03 2019 00:00:00.000

5

'R'

Sat Jun 01 2019 00:00:00.000

5

'S'

Sun Jun 02 2019 00:00:00.000

5

'T'

Mon Jun 03 2019 00:00:00.000

6

'A'

Mon Jul 01 2019 00:00:00.000

6

'B'

Tue Jul 02 2019 00:00:00.000

6

'C'

Wed Jul 03 2019 00:00:00.000

7

'R'

Thu Aug 01 2019 00:00:00.000

7

'S'

Fri Aug 02 2019 00:00:00.000

7

'T'

Sat Aug 03 2019 00:00:00.000

8

'R'

Sun Sep 01 2019 00:00:00.000

8

'S'

Mon Sep 02 2019 00:00:00.000

8

'T'

Tue Sep 03 2019 00:00:00.000

9

'R'

Tue Oct 01 2019 00:00:00.000

9

'S'

Wed Oct 02 2019 00:00:00.000

9

'T'

Thu Oct 03 2019 00:00:00.000

10

'X'

Fri Nov 01 2019 00:00:00.000

10

'Y'

Sat Nov 02 2019 00:00:00.000

10

'Z'

Sun Nov 03 2019 00:00:00.000

Cases

CASE_ID : INT

CITY : STRING

1

'Casablanca'

2

'Casablanca'

3

'Casablanca'

4

'Casablanca'

5

'Casablanca'

6

'Göttingen'

7

'Göttingen'

8

'Göttingen'

9

'Göttingen'

10

'Göttingen'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : STRING

Column2 : STRING

Column3 : INT

'Casablanca'

'A, B, C'

4

'Göttingen'

'A, B, C'

1

'Casablanca'

'R, S, T'

1

'Göttingen'

'R, S, T'

3

'Göttingen'

'X, Y, Z'

1

[15] In this step we add:

  • a count of how often a variant occurs per city.

  • an index order, which gives an index sorted by how often the different variants occur, per city.

Query

Column1

"Cases"."CITY"

Column2

VARIANT("Activities"."ACTIVITY")

Column3

COUNT("Cases"."CASE_ID")

Column4

INDEX_ORDER(PU_COUNT(DOMAIN_TABLE(VARIANT("Activities"."ACTIVITY"), "Cases"."CITY"), "Cases"."CASE_ID"), DESC, GROUP(PU_FIRST(DOMAIN_TABLE(VARIANT("Activities"."ACTIVITY"), "Cases"."CITY"), "Cases"."CITY") ) )

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Fri Feb 01 2019 00:00:00.000

1

'B'

Sat Feb 02 2019 00:00:00.000

1

'C'

Sun Feb 03 2019 00:00:00.000

2

'A'

Fri Mar 01 2019 00:00:00.000

2

'B'

Sat Mar 02 2019 00:00:00.000

2

'C'

Sun Mar 03 2019 00:00:00.000

3

'A'

Mon Apr 01 2019 00:00:00.000

3

'B'

Tue Apr 02 2019 00:00:00.000

3

'C'

Wed Apr 03 2019 00:00:00.000

4

'A'

Wed May 01 2019 00:00:00.000

4

'B'

Thu May 02 2019 00:00:00.000

4

'C'

Fri May 03 2019 00:00:00.000

5

'R'

Sat Jun 01 2019 00:00:00.000

5

'S'

Sun Jun 02 2019 00:00:00.000

5

'T'

Mon Jun 03 2019 00:00:00.000

6

'A'

Mon Jul 01 2019 00:00:00.000

6

'B'

Tue Jul 02 2019 00:00:00.000

6

'C'

Wed Jul 03 2019 00:00:00.000

7

'R'

Thu Aug 01 2019 00:00:00.000

7

'S'

Fri Aug 02 2019 00:00:00.000

7

'T'

Sat Aug 03 2019 00:00:00.000

8

'R'

Sun Sep 01 2019 00:00:00.000

8

'S'

Mon Sep 02 2019 00:00:00.000

8

'T'

Tue Sep 03 2019 00:00:00.000

9

'R'

Tue Oct 01 2019 00:00:00.000

9

'S'

Wed Oct 02 2019 00:00:00.000

9

'T'

Thu Oct 03 2019 00:00:00.000

10

'X'

Fri Nov 01 2019 00:00:00.000

10

'Y'

Sat Nov 02 2019 00:00:00.000

10

'Z'

Sun Nov 03 2019 00:00:00.000

Cases

CASE_ID : INT

CITY : STRING

1

'Casablanca'

2

'Casablanca'

3

'Casablanca'

4

'Casablanca'

5

'Casablanca'

6

'Göttingen'

7

'Göttingen'

8

'Göttingen'

9

'Göttingen'

10

'Göttingen'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : STRING

Column2 : STRING

Column3 : INT

Column4 :

'Casablanca'

'A, B, C'

4

1

'Casablanca'

'R, S, T'

1

2

'Göttingen'

'A, B, C'

1

2

'Göttingen'

'R, S, T'

3

1

'Göttingen'

'X, Y, Z'

1

3

[16] Finally we want to report the fraction of cases which are covered by the two most common process variants, per city. To calculate the fraction, we use the PU_COUNT function to count cases per city:

  • Numerator: Inside the PU_COUNT function we only select the variants that are among the two most common (< 3) for that city.

  • Denominator: We apply the PU_COUNT function on all variants.

Query

Column1

"Cases"."CITY"

Column2

COUNT("Cases"."CASE_ID")

Column3

PU_COUNT(DOMAIN_TABLE("Cases"."CITY"), "Cases"."CASE_ID", INDEX_ORDER(PU_COUNT(DOMAIN_TABLE(VARIANT("Activities"."ACTIVITY"), "Cases"."CITY"), "Cases"."CASE_ID"), DESC, GROUP(PU_FIRST(DOMAIN_TABLE(VARIANT("Activities"."ACTIVITY"), "Cases"."CITY"), "Cases"."CITY") ) ) < 3) / PU_COUNT(DOMAIN_TABLE("Cases"."CITY"), "Cases"."CASE_ID")

Input

Output

Activities

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Fri Feb 01 2019 00:00:00.000

1

'B'

Sat Feb 02 2019 00:00:00.000

1

'C'

Sun Feb 03 2019 00:00:00.000

2

'A'

Fri Mar 01 2019 00:00:00.000

2

'B'

Sat Mar 02 2019 00:00:00.000

2

'C'

Sun Mar 03 2019 00:00:00.000

3

'A'

Mon Apr 01 2019 00:00:00.000

3

'B'

Tue Apr 02 2019 00:00:00.000

3

'C'

Wed Apr 03 2019 00:00:00.000

4

'A'

Wed May 01 2019 00:00:00.000

4

'B'

Thu May 02 2019 00:00:00.000

4

'C'

Fri May 03 2019 00:00:00.000

5

'R'

Sat Jun 01 2019 00:00:00.000

5

'S'

Sun Jun 02 2019 00:00:00.000

5

'T'

Mon Jun 03 2019 00:00:00.000

6

'A'

Mon Jul 01 2019 00:00:00.000

6

'B'

Tue Jul 02 2019 00:00:00.000

6

'C'

Wed Jul 03 2019 00:00:00.000

7

'R'

Thu Aug 01 2019 00:00:00.000

7

'S'

Fri Aug 02 2019 00:00:00.000

7

'T'

Sat Aug 03 2019 00:00:00.000

8

'R'

Sun Sep 01 2019 00:00:00.000

8

'S'

Mon Sep 02 2019 00:00:00.000

8

'T'

Tue Sep 03 2019 00:00:00.000

9

'R'

Tue Oct 01 2019 00:00:00.000

9

'S'

Wed Oct 02 2019 00:00:00.000

9

'T'

Thu Oct 03 2019 00:00:00.000

10

'X'

Fri Nov 01 2019 00:00:00.000

10

'Y'

Sat Nov 02 2019 00:00:00.000

10

'Z'

Sun Nov 03 2019 00:00:00.000

Cases

CASE_ID : INT

CITY : STRING

1

'Casablanca'

2

'Casablanca'

3

'Casablanca'

4

'Casablanca'

5

'Casablanca'

6

'Göttingen'

7

'Göttingen'

8

'Göttingen'

9

'Göttingen'

10

'Göttingen'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : STRING

Column2 : INT

Column3 : FLOAT

'Göttingen'

5

0.8

'Casablanca'

5

1.0