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:
The above behavior also holds for descending ordering of indices:
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.
Examples
Column1
INDEX_ORDER ( "Table1"."Date")
Column1
"companyDetail"."country"
Column2
INDEX_ORDER("caseTable"."value")
Column1
"companyDetail"."country"
Column2
INDEX_ORDER(COUNT_TABLE("caseTable"))
Column1
INDEX_ORDER ( "Table1"."Date")
Column1
"countryDetail"."country"
Column2
INDEX_ORDER("countryDetail"."value", GROUP("countryDetail"."country") )
caseTable
countryDetail
Foreign Keys
countryDetail.value | caseTable.value |
Column1
"countryDetail"."country"
Column2
"countryDetail"."city"
Column3
INDEX_ORDER("countryDetail"."value", GROUP("countryDetail"."country", "countryDetail"."city") )
caseTable
countryDetail
Foreign Keys
countryDetail.value | caseTable.value |
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:
Column1
"Cases"."CITY"
Column2
VARIANT("Activities"."ACTIVITY")
Column3
COUNT("Cases"."CASE_ID")
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
Foreign Keys
Cases.CASE_ID | Activities.CASE_ID |
- 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.
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") ) )
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
Foreign Keys
Cases.CASE_ID | Activities.CASE_ID |
- 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.
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")
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
Foreign Keys
Cases.CASE_ID | Activities.CASE_ID |