I’m writing this blog because I encountered the same problem again and again and my google searches weren’t bringing me success, so I hope that this blog will pop up for the next person, who would like to pull string / text data from one column into one cell, based on another column’s variant.
What is Concatenate good for?
Concatenate can pull string / text data in one cell with a chosen separator (comma, slash, etc.). It is very useful when you would like to group your data by a certain column to have one row per variable, but you would like to also keep all the related text data from a different column, but it has also different variables.
For example let’s take look here. We have a data (original data: Alteryx Challange 338) set that contains Artist names, Genres and Albums. When we would like to have one row per Artist that contains 3 columns with the Artist name, the Genres and the Albums we can utilize the Transform Tool with the Concatenate option.
We just have to drag the Transform Tool to the Canvas and configurate in the following way:
1. Click on Artist name à Click Add à Select Group By
2. Click on Album à Click Add à Hover over String à Select Concatenate
3. Once you did that, you should see this extra Configuration window (blue frames). Here you can set the kind of separator you would like to use to separate the different data coming from different rows. Since we have comma now within one cell, I chose comma and space as a separator: “,\s”.
Repeat Step 2 and Step 3 with Genres and ta-da, we have one row for each Artist with all Genres and Albums from the dataset:
