GROUPCONCATDISTINCT

Syntax

GROUPCONCATDISTINCT(<any>[; <number>])

Description

Creates a list of all unique non-null values seen in a group (currently, max. 1000 values are collected). 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]

This function returns a list. If you require a JSON represented as a string, wrap this function in TOJSON (E.g., TOJSON(<function>(...)).