Use the GROUPCONCAT function to merge data from multiple rows into one field. GROUPCONCAT is a GROUP BY function and returns a string if the group contains at least 1 non-null value, if no non-null values exist, it returns a null value.
Syntax: GROUPCONCAT(groupedDimension, keyDimension, seperator)
- groupedDimension - the dimension which values are grouped.
- keyDimension - the values according to which the groupedDimension are grouped.
For example, you can apply GROUPCONCAT to concatenate the values of your campaign name (groupedDimension) and campaign start date (keyDimension), with the specified separator between the values. The concatenation is unique, with every value appearing one time.
| Campaign Name | Campaign Start Date |
|---|---|
| Winter | 01/01/22 |
| Fall - sales | 12/01/22 |
| Q1 – new arrivals | 01/01/22 |
| Christmas | 12/01/22 |
Formula: GROUPCONCAT('Campaign Name', 'Campaign Start Date', ',')
Output:
| Campaign Name | Campaign Start Date |
|---|---|
| Q1 – new arrivals, Winter | 01/01/22 |
| Christmas, Fall – sales | 12/01/22 |
Note
- In order for the function to work correctly a value in the keyDimension must be linked to several values in the groupedDimension. This principle works between hierarchies and between attributes and keys. Fields with a one-to-many relationship can be used in groupconcat.
- The data table GROUPCONCAT runs on changes according to the setting the function is applied on. In the mapping screen, the function runs on the source file, and in calculated dimensions, the function runs on the workspace's database.
- When using this function in calculated dimensions, you can’t use variables such as JavaScript or other calculated dimensions as the keyDimension or the groupedDimension. Variables and calculated dimensions are calculated, per row, upon execution of a query, and aren’t saved in the workspace's database. When variables are used in GROUPCONCAT the function fails.