Applies to: CELONIS 4.7
Description
The PU_STRING_AGG operator returns the concatenation of strings from the given parent column. The delimiter
will be always inserted between the concatenation of the strings. Multiple order by expressions can be used
in order to determine the order of the concatenation.
The PU_STRING_AGG function can only be applied to STRINGs.
Syntax
PU_STRING_AGG ( child, parent.column, delimiter [, filter_expression ] [, ORDER BY parent_table.column [ASC|DESC] ] )
- child_table: The table to which the aggregation result should be pulled. This can be:
- a table from the data model. This table needs to be a child table of the parent_table. Documentation about parent and child tables can be found in Join functionality.
- DOMAIN_TABLE.
- CONSTANT.
- parent_table.column: The column which should be aggregated for every row of the child_table.
- delimiter: A delimiter STRING what should be used to separate the strings in the result.
- filter_expression (optional): An optional filter expression to specify which values of the parent_table.column should be taken into account for the aggregation.
- ORDER BY (optional): Elements of the specified column are used to determine the order in which the strings should be concatenated in the result. After the column, ASC (for ascending direction) or DESC (descending direction) can be specified. If the order direction is not specified, the ascending (ASC) order is used.
NULL handling
If no value in the parent table exists for the element in the child table (either because all values of the parent table are filtered out, or because no corresponding value exists in the first place), NULL will be returned. NULL values in the parent table column are treated as if the row does not exist.
Examples
[1] Aggregate the activity column per case.
Query
Column1
PU_STRING_AGG ( "caseTable" , "activityTable"."activity" , ' - ' )
Output
Result
| Column1 : STRING |
|---|
| 'A - B' |
| 'C - D' |
[2] Aggregate the activity column per casewith respect to the ascending sorted order of value 2 and the descending
sorted order of value 1.
Query
Column1
PU_STRING_AGG (
"caseTable" ,
"activityTable"."activity" ,
' | ' ,
ORDER BY "activityTable"."value2" ASC , "activityTable"."value1" DESC
)
Input
activityTable
| caseId : STRING | activity : STRING | timestamp : DATE | value1 : INT | value2 : INT |
|---|---|---|---|---|
| '3' | 'Y' | Thu Jan 02 2020 12:00:00.000 | 7 | 10 |
| '1' | 'A' | Sat Jan 04 2020 22:00:00.000 | 31 | 15 |
| '1' | 'B' | Sun Jan 05 2020 23:00:00.000 | 62 | 12 |
| '2' | 'C' | Mon Jan 06 2020 12:00:00.000 | 12 | 19 |
| '2' | 'D' | Mon Jan 06 2020 14:00:00.000 | 14 | 9 |
| '2' | 'E' | Mon Jan 06 2020 20:00:00.000 | 3 | 9 |
| '3' | 'X' | Tue Jan 07 2020 01:00:00.000 | 31 | 9 |
| '2' | 'F' | Wed Jan 08 2020 08:00:00.000 | 15 | 9 |
| '2' | 'G' | Wed Jan 08 2020 10:00:00.000 | 4 | 21 |
| '3' | 'Z' | Thu Jan 09 2020 12:00:00.000 | 8 | 32 |
caseTable
| caseId : STRING |
|---|
| '1' |
| '2' |
| '3' |
Foreign Keys
| activityTable.caseId | caseTable.caseId |
Output
Result
| Column1 : STRING |
|---|
| 'B | A' |
| 'F | D | E | C | G' |
| 'X | Y | Z' |
[3] Aggregate the activity column per case with respect to the ascending sorted order of value while only the
activities which have a value between 10 and 30 are concatenated.
Query
Column1
PU_STRING_AGG (
"caseTable" ,
"activityTable"."activity" ,
' | ' ,
"activityTable"."value" BETWEEN 10 AND 30 ,
ORDER BY "activityTable"."value"
)
Input
activityTable
| caseId : STRING | activity : STRING | timestamp : DATE | value : INT |
|---|---|---|---|
| '3' | 'Y' | Thu Jan 02 2020 12:00:00.000 | 10 |
| '1' | 'A' | Sat Jan 04 2020 22:00:00.000 | 15 |
| '1' | 'B' | Sun Jan 05 2020 23:00:00.000 | 12 |
| '2' | 'C' | Mon Jan 06 2020 12:00:00.000 | 19 |
| '2' | 'D' | Mon Jan 06 2020 14:00:00.000 | 34 |
| '2' | 'E' | Mon Jan 06 2020 20:00:00.000 | 9 |
| '3' | 'X' | Tue Jan 07 2020 01:00:00.000 | 9 |
| '2' | 'F' | Wed Jan 08 2020 08:00:00.000 | 17 |
| '2' | 'G' | Wed Jan 08 2020 10:00:00.000 | 21 |
| '3' | 'Z' | Thu Jan 09 2020 12:00:00.000 | 32 |
caseTable
| caseId : STRING |
|---|
| '1' |
| '2' |
| '3' |
Foreign Keys
| activityTable.caseId | caseTable.caseId |
Output
Result
| Column1 : STRING |
|---|
| 'B | A' |
| 'F | C | G' |
| 'Y' |
[4] Aggregate the activities per case where
country is not 'FR' while the aggregation of the activities happens with
respect to the ascending order of the eventtimes from the activityTable
Query
Column1
PU_STRING_AGG (
"companyDetail" ,
"activityTable"."activity" ,
',' ,
"companyDetail"."country" <> 'FR' ,
ORDER BY "activityTable"."eventtime"
)
Input
activityTable
| caseId : INT | activity : STRING | eventtime : DATE |
|---|---|---|
1 | 'A' | Thu Dec 31 2015 22:13:20.000 |
1 | 'B' | Fri Jan 01 2016 01:00:00.000 |
1 | 'C' | Fri Jan 01 2016 03:46:40.000 |
2 | 'A' | Thu Dec 31 2015 22:30:00.000 |
2 | 'E' | Thu Dec 31 2015 22:46:40.000 |
2 | 'F' | Thu Dec 31 2015 22:46:40.000 |
2 | 'G' | Thu Dec 31 2015 23:03:20.000 |
3 | 'A' | Fri Jan 01 2016 03:46:40.000 |
3 | 'H' | Fri Jan 01 2016 06:33:20.000 |
4 | 'A' | Thu Dec 31 2015 22:15:00.000 |
5 | 'A' | Thu Dec 31 2015 22:16:40.000 |
5 | 'B' | Thu Dec 31 2015 22:18:20.000 |
5 | 'C' | Thu Dec 31 2015 22:20:00.000 |
6 | 'A' | Thu Dec 31 2015 22:14:10.000 |
7 | 'A' | Fri Jan 01 2016 17:40:00.000 |
8 | 'A' | Fri Jan 01 2016 20:26:40.000 |
companyDetail
companyResource
Foreign Keys
| companyDetail.caseId | activityTable.caseId |
| companyResource.caseId | activityTable.caseId |
Output
Result
| Column1 : STRING |
|---|
| 'A,B,C' |
| 'A,E,F,G' |
| 'A,H' |
| 'A' |
| 'A,B,C' |
| null |
| 'A' |
| 'A' |
[5] One can use the INDEX_ORDER to aggregate only distinct values.
The example below calculates the distinct currencies involved in each case.
Query
Column1
PU_STRING_AGG (
"caseTable" ,
"activityTable"."currency" ,
'-' ,
INDEX_ORDER (
"activityTable"."currency" ,
GROUP ( "caseTable"."caseId" , "activityTable"."currency" )
)
=
1
)
Input
activityTable
caseTable
Foreign Keys
| activityTable.caseId | caseTable.caseId |
Output
Result
| Column1 : STRING |
|---|
| 'EUR-USD' |
| 'USD' |