Skip to main content

Concat - script function

Concat() is used to combine string values. The script function returns the aggregated string concatenation of all values of the expression iterated over a number of records as defined by a group by clause.

Syntax:  

Concat ([ distinct ] string [, delimiter [, sort-weight]])

Return data type: string

Arguments:  

The expression or field containing the string to be processed.

Arguments
Argument Description
string

The expression or field containing the string to be processed.

delimiter Each value may be separated by the string found in delimiter.
sort-weight

The order of concatenation may be determined by the value of the dimension sort-weight, if present, with the string corresponding to the lowest value appearing first in the concatenation.

distinct If the word distinct occurs before the expression, all duplicates are disregarded.

Examples and results:  

Add the example script to your app and run it. To see the result, add the fields listed in the results column to a sheet in your app.

Examples and results
Example Result Results once added to a sheet

TeamData:

LOAD * inline [

SalesGroup|Team|Date|Amount

East|Gamma|01/05/2013|20000

East|Gamma|02/05/2013|20000

West|Zeta|01/06/2013|19000

East|Alpha|01/07/2013|25000

East|Delta|01/08/2013|14000

West|Epsilon|01/09/2013|17000

West|Eta|01/10/2013|14000

East|Beta|01/11/2013|20000

West|Theta|01/12/2013|23000

] (delimiter is '|');

 

Concat1:

LOAD SalesGroup,Concat(Team) as TeamConcat1 Resident TeamData Group By SalesGroup;

SalesGroup

East

West

TeamConcat1

AlphaBetaDeltaGammaGamma

EpsilonEtaThetaZeta

Given that the TeamData table is loaded as in the previous example:

LOAD SalesGroup,Concat(distinct Team,'-') as TeamConcat2 Resident TeamData Group By SalesGroup;

SalesGroup

East

West

TeamConcat2

Alpha-Beta-Delta-Gamma

Epsilon-Eta-Theta-Zeta

Given that the TeamData table is loaded as in the previous example. Because the argument for sort-weight is added, the results are ordered by the value of the dimension Amount:

LOAD SalesGroup,Concat(distinct Team,'-',Amount) as TeamConcat2 Resident TeamData Group By SalesGroup;

SalesGroup

East

West

TeamConcat2

Delta-Beta-Gamma-Alpha

Eta-Epsilon-Zeta-Theta

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!