transformations:concatenatetext
Table of Contents
CONCATENATE TEXT
Category: Transform / Basic
Description
This action concatenates text values into delimited text.
Use cases
- When exporting to a target database or system which requires a value list in a single field.
- Building a column that contains a list of tags.
- To build a list of values within an API call.
Action settings
| Mode | Description |
|---|---|
| Concatenate rows | Concatenates a column's values in multiple rows into a single row, separated by the chosen delimiter. |
| Concatenate columns | Concatenates the values of different columns into a single column, separated by the chosen delimiter. |
Concatenate rows settings
| Setting | Description |
|---|---|
| Concatenate | Select the column to concatenate the values of. |
| Distinct | When selected, only the unique values in the column will appear in the concatenated result. |
| Ignore empty values | When selected, only values that are not empty will appear in the concatenated result. |
| Separator | Select the character used to delimit the column values. Options: Comma, Semicolon, Tab, Pipe, Tilde, Space, Line break, Custom (and select a character), or No separator. |
| Concatenate more columns | Configure additional columns to be included in the output dataset. |
| Group by | Select the column(s) to group the concatenated columns by. (optional) |
Concatenate columns settings
| Setting | Description |
|---|---|
| New column name | Name of the new column resulting from the concatenation of existing columns. |
| Columns to concatenate | Select which columns to concatenate. Options: All columns, Selected columns, All except selected columns |
| Remove duplicates | When selected, a value that repeats within the same row appears only once. |
| Ignore empty values | When selected, only values that are not empty will appear in the concatenated result. |
| Don't remove concatenated columns | When selected, concatenated columns will remain in the table. |
| Separator | Select the character used to delimit the column values. Options: Comma, Semicolon, Tab, Pipe, Tilde, Space, Line break, Custom (and select a character), or No separator. |
Remarks
Concatenate rows remarks
The output dataset will consist only of the columns selected to be concatenated, and the Group by columns chosen, if any.
This action concatenates the column values in the order they are encountered. If the concatenated values are to appear in a specific order (i.e., ascending or descending), the column will need to be sorted prior to running this action.
Examples
Example #1 (Concatenate rows)
Given the table of responses to questions below, summarize the response values.
Before (source table)
| Question | Response |
|---|---|
| Q1 | 1 |
| Q2 | 3 |
| Q3 | 4 |
| Q1 | 2 |
| Q2 | 3 |
| Q3 | 5 |
| Q1 | 3 |
| Q2 | 1 |
| Q3 | 3 |
After (result table)
| Response |
|---|
| 1,3,4,2,3,5,3,1,3 |
Action parameters:
Concatenate: Response
Separator: Comma
If Distinct is selected:
| Response |
|---|
| 1,3,4,2,5 |
Note: The Response column would need to be sorted prior to this action to have the values concatenated in numeric order (i.e., 1,2,3,4,5).
Example #2 (Concatenate columns)
Given the food choices found in multiple columns, summarize them into a single column.
Before (source table)
| Starter | Main course | Dessert |
|---|---|---|
| Salad | Meat | Cake |
| Soup | Fish | Fruit |
| Pasta | Fish | Cake |
| Soup | Meat | Fruit |
| Salad | Fish | Fruit |
| Pasta | Meat | Cake |
| Pasta | Fish | Cake |
| Soup | Meat | Fruit |
After (result table)
| Dinner |
|---|
| Salad;Meat;Cake |
| Soup;Fish;Fruit |
| Pasta;Fish;Cake |
| Soup;Meat;Fruit |
| Salad;Fish;Fruit |
| Pasta;Meat;Cake |
| Pasta;Fish;Cake |
| Soup;Meat;Fruit |
Action parameters:
New column name: Dinner
Columns to concatenate: All columns
Separator: Semicolon
See also
transformations/concatenatetext.txt · Last modified: by roberto