Skip navigation

Concatenate field values across rows into comma-separated list

score 145
You have not voted. Active

Very often we've got a set of values across different rows (could be records or could be query results) and want to generate a comma (or other) separated list. One use case is where we want to show all values in a title as covered in this Idea: http://community.tableau.com/ideas/3054. Another use case is where we want to generate a separated list of values for export, for example as covered in this forum thread: How to group by data.

 

There are a couple of techniques for doing this for display purposes covered in that latter thread, one uses table calcs (see 15. Making a String List for details) and the other involves editing the Text Shelf. However, both are inadequate when it comes to issues of complexity, export, use for following fields, etc.

 

What I'd like to see is a function that automatically does the concatenation into a string list. I could see two different ways of building this in Tableau:

 

1) a table calculation

2) a level of detail expression

 

Personally, I prefer the level of detail expression option because it would enable us to build a view that includes the results of the expression without it affecting the level of detail of the view (unlike table calculations) and all the complexities that adds to the view.

 

I don't know how the syntax would work, the key options would be:

- context (something like what LOD calculations use)

- separator

- optional last record separator (to enable use of "and" and Oxford commas)

 

For example, given a set of values [apples, cherries, bananas] one could build:

 

apples-cherries-bananas

apples, cherries, bananas

apples, cherries, and bananas

 

Jonathan

Comments

Vote history