GROUPCONCATDISTINCT
- Charles Bishop (Deactivated)
Analytics
Syntax
GROUPCONCATDISTINCT(<any>[; <number>])
Description
Concatenates all non-null values seen in a group. The first argument is the column containing values that should be concatenated. The second value is an optional constant used to set a maximum number of values to avoid overflow problems with very large groups. The maximum number of values defaults to 1000. If more than the configured maximum number n of values are seen, the top n values in ascending order are kept.
The concatenated values are returned as a list.
This is an aggregate function.
Example
Given the following data:
| Groups | Items |
|---|---|
| group1 | 2 |
| group1 | 5 |
| group1 | <null> |
| group1 | 17 |
| group1 | 8 |
| group2 | <null> |
| group2 | 6 |
| group2 | 6 |
| group2 | 5 |
| group2 | 12 |
| group3 | <null> |
| group3 | <null> |
| group3 | 22 |
| group3 | 25 |
| group3 | 12 |
First create a group using GROUPBY(#RawData!Groups)
| Groups |
|---|
| group1 |
| group2 |
| group3 |
Then use the GROUPCONCATDISTINCT(#RawData!Items;2) and the result is the non-null values concatenated in the number of groups you specified by the constant.
| Groups | Item_CONCAT |
|---|---|
| group1 | [2, 5] |
| group2 | [5, 6] |
| group3 | [12, 22] |