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 table for example.

It has two columns - Name & Orders. The column “Name” essentially has only two values - Harry & Ron - repeating over.

Table 11 Hogwarts shopping list

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:

Table 12 Collapsed rows

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.

Table 13 Fruit diet

Name

Fruit

Harry

Apple

Ron

Mango

Hermione

Kiwi

Harry

Mango

Ron

Apple

Hermione

Mango

Harry

Apple

Ron

Grapes

Hermione

Grapes

When we concatenate the Fruit column and Group it by Name, the above table becomes this:

Table 14 Fruit diet - Concatenation of Fruit

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:

Table 15 Fruit diet - Concatenation of Name

Fruit

Concatenation of Name

Apple

Harry, Ron

Grapes

Ron, Hermione

Kiwi

Hermione

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:

Table 16 Diet chart

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:

Table 17 Diet chart - Concatenation of Category

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.

Note

  1. 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.

  2. 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.

Note

Cells with concatenated values can have upto a maximum of 50 unique values. Other values are ignored.