Skip to content

String Aggregation functions

Concat

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.

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

Return data type: string

The expression or field containing the string to be processed.

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 , 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.
ExampleResult
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: LOAD SalesGroup,Concat(distinct Team,'-',Amount) as TeamConcat2
Resident TeamData Group By SalesGroup;
Because the argument for sort-weight is added, the results are ordered by the value of the dimension Amount.
SalesGroup
East
West
TeamConcat2
Delta-Beta-Gamma-Alpha
Eta-Epsilon-Zeta-Theta

FirstValue

FirstValue() returns the value that was loaded first from the records defined by the expression, sorted by a group by clause.

FirstValue(expr)

Return data type: dual

Argument Description
expr The expression or field containing the data to be measured.

If no text value is found, NULL is returned.

Example Result  
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 '|');
 
FirstValue1:
LOAD SalesGroup,FirstValue(Team) as FirstTeamLoaded Resident TeamData Group By SalesGroup;
SalesGroup
East
West
FirstTeamLoaded
Gamma
Zeta

LastValue

LastValue() returns the value that was loaded last from the records defined by the expression, sorted by a group by clause.

LastValue(expr)

Return data type: dual

Argument Description
expr The expression or field containing the data to be measured.

If no text value is found, NULL is returned.

Example Result  
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 '|');
 
LastValue1:
LOAD SalesGroup,LastValue(Team) as LastTeamLoaded Resident TeamData Group By SalesGroup;
SalesGroup
East
West
LastTeamLoaded
Beta
Theta

MaxString

MaxString() finds string values in the expression and returns the last text value sorted over a number of records, as defined by a group by clause.

MaxString(expr)

Return data type: dual

Argument Description
expr The expression or field containing the data to be measured.

If no text value is found, NULL is returned.

Example Result  
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,MaxString(Team) as MaxString1 Resident TeamData Group By SalesGroup;
SalesGroup
East
West
MaxString1
Gamma
Zeta
Given that the TeamData table is loaded as in the previous example, and your data load script has the SET statement:
SET DateFormat='DD/MM/YYYY';
LOAD SalesGroup,MaxString(Date) as MaxString2
Resident TeamData Group By SalesGroup;
SalesGroup

East
West
MaxString2
01/11/2013
01/12/2013

MinString

MaxString() finds string values in the expression and returns the first text value sorted over a number of records, as defined by a group by clause.

MinString(expr)

Return data type: dual

Argument Description
expr The expression or field containing the data to be measured.

If no text value is found, NULL is returned.

Example Result  
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,MinString(Team) as MinString1 Resident TeamData Group By SalesGroup;
SalesGroup
East
West
MinString1
Alpha
Epsilon
Given that the TeamData table is loaded as in the previous example, and your data load script has the SET statement:
SET DateFormat='DD/MM/YYYY';
LOAD SalesGroup,MinString(Date) as MinString2
Resident TeamData Group By SalesGroup;
SalesGroup
East
West
MinString2
01/05/2013
01062/2013