• Have you ever wondered what the difference is between unique concatenate and concatenate?
  • Have you ever accidentally used concatenate and gotten a mess of a result?

I taught a Spotfire Essentials course this week, and one of the things that ALWAYS comes up in this course is — what is the difference between concatenate and unique concatenate?

The concatenate and unique concatenate expressions are frequently used and frequently confused. Concatenate is used in custom expressions when users want to string together two or more columns of information as shown in the screenshots below.  See first the expression and then the result.

Resulting data…

Concatenate AND unique concatenate are also both found in pivot transformations.  When a user adds a string column to the transfer columns (as shown in the screen shot below), Spotfire defaults to the concatenate aggregation method.  In this particular use case, the user should almost always change the aggregation method from concatenate to unique concatenate.  If the user is trying to transfer a column called “Field” with unique values of “Bakken, Permian, and Marcellus”, concatenate will return “Bakken, Permian, Marcellus” for EVERY record.  Chances are, what the user actually wants is a single value that is the Field for a particular record.  Using unique concatenate rather than concatenate will fix this problem.

In the pivot…

Resulting data…

After changing the aggregation to unique concatenate.

Hopefully that clarifies any confusion you might have had on the two functions.  Send any questions to julie@bigmountainanalytics.com.

Written by Julie Schellberg of Big Mountain Analytics, LLC
Residing in Whitefish, MT, an analytics partner with Ruths.ai. Specializing in Spotfire analytics, dabbling in Power BI and R.