Concatenate Values
The Concatenate Values function makes your dataset compact for analysis. You may come across datasets that contain repetitive values for one or more columns. These datasets can be a little difficult to work on in their original format.
See the following Fruit Diet table for example.
It has two columns - Name & Orders. The column “Name” essentially has only two values - Harry & Ron - repeating over.
| Name | Orders |
|---|---|
| Harry | Wand |
| Ron | Wand |
| Harry | Invisible cloak |
| Harry | Nimbus 2000 |
| Ron | Marauder's Map |
| Ron | Magic frogs |
| Ron | Robe |
| Harry | Robe |
| Harry | Jelly Beans |
The Concatenate Values function allows you to merge values in rows within a text column and map them to one or more columns, irrespective of it's datatype. So after the concatenation of rows, the above dataset would look something like this:
| Name | Concatenation of Orders |
|---|---|
| Harry | Invisible cloak, Jelly beans, Nimbus 2000, Robe, Wand |
| Ron | Magic frogs, Marauder’s map, Robe, Wand |
Here, we have concatenated the orders column and grouped them by the name column. Note the values in the concatenated cell appears alphabetically.
Let’s look at another dataset to make this clear.
| Name | Fruit |
|---|---|
| Harry | Apple |
| Ron | Mango |
| Harry | Kiwi |
| Harry | Mango |
| Ron | Apple |
| Ron | Mango |
| Ron | Apple |
| Harry | Grapes |
| Harry | Grapes |
When we concatenate the Fruit column and Group it by Name, the above table becomes this:
| Name | Concatenation of Fruit |
|---|---|
| Harry | Apple, Mango |
| Ron | Apple, Grapes, Mango |
| Hermione | Grapes, Kiwi, Mango |
Let’s take Table Fruit Diet again but this time we’ll concatenate the names and group them by fruits. It would look something like this:
| Fruit | Concatenation of Name |
|---|---|
| Apple | Harry, Ron |
| Grapes | Ron, Hermione |
| Kiwi | Hermoine |
| Mango | Harry, Ron, Hermione |
Concatenating values and grouping it by multiple columns
You can also concatenate values by grouping them by more than one column.
See the following table for example:
| Name | Fruit | Category |
|---|---|---|
| Harry | Apple | Fruit |
| Harry | Apple | Citrus |
| Ron | Banana | Fruit |
| Ron | Orange | Citrus |
| Ron | Orange | Fruit |
| Hermione | Banana | Fruit |
When we concatenate values in the Category column and group it by the remaining two columns, this is the result we get:
| Name | Fruit | Concatenation of Category |
|---|---|---|
| Harry | Apple | Citrus, Fruit |
| Ron | Banana | Fruit |
| Ron | Orange | Citrus, Fruit |
| Hermione | Banana | Fruit |
Using the Concatenate Values function
Now that you understand the Concatenate Values function. Let’s see how you can use it in Mammoth:
- Go to Transform > Reshape, Group and Aggregate.
- Select the Concatenate Values function.
- Define specifics and apply changes.
- You can rename the concatenated column and choose an appropriate delimiter to segregate the values as well. By default, these values are set as "Concatenation of {column_name} and ,, respectively.
- The system does not allow the concatenated column to be renamed to any of the chosen Group by columns.
You can also concatenate values and choose to group it by multiple columns. Here's how you can do that:
- Navigate to your Dataset in your Data Library.
- Go to Transform > Reshape, Group and Aggregate > Concatenate Values.
- Define specifics, apply changes and you're done.
Cells with concatenated values can have upto a maximum of 50 unique values. Other values are ignored.