Examples of concat functions

The CONCAT() function should not be confused with the CONCATENATE script keyword.

In its simplest form, Concat() is used to string / join together values/words/selections into one string. However, it can be utilized in a number of ways to help you solve different problems. All the examples are based upon the following table:

Simple String Concatenation

As mentioned earlier, the concat function lets you string together a list of values. These values can be hard coded or driven by selections/data.

=CONCAT(MyColumn,',')

=CONCAT(DISTINCT MyColumn,',')

This simple concat statement would string together all of the possible values from the column MyColumn. You may wish to add the DISTINCT keyword. This would ensure that each value is only displayed once in the string:

ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX

When using a simple concat, you have the option to add a sort weight to the function to order the string values by a column of your choice. In the example below, the date column is added to sort the values.

=CONCAT(MyColumn, ',', Date)

Result: JKL,VWX,GHI,ABC,STU,PQR,MNO,DEF

Concat() within an Expression/Set Statement

Example: Passing multiple dynamic values to a set analysis expression

There are occasions when you want to pass a dynamic selection of values to a set statement. To do this you need to add some single quotes to the string so that the CONCAT() function returns e.g. 'JKL','VWX'. But you cannot have the single quotes as they are, since they then would be interpreted when the Concat is evaluated instead of when the set expression is evaluated. Instead, use the Chr() function:

=CONCAT(Chr(39)&MyColumn&Chr(39),',')

You can then pass this concat statement to the inside of an expression:

=Sum({<MyColumn={$(=CONCAT(Chr(39)&MyColumn&Chr(39),','))}>} Value)

In most instances, this technique would be used where data islands are present. It lets you pass values to an expression that will not affect any part of the data model as the data island table is not joined to it.

Concat() in the Script

Concat can also be used in the script to convert multiple rows into one single column value, just as any other aggregation.

Remembering the source data used earlier, the result of script side CONCAT is as follows:

The following is the result of this script function:

Using Rank() to influence the Concat()

When you start to utilize other function in conjunction with concat() you can start to achieve clever results. In this example, Rank() has been used to grab the top three performers (based on Value) and string them together.

=CONCAT(IF(aggr(Rank(sum(Value)),MyColumn)<=3,MyColumn),',')

Result: ABC,MNO,STU