ARRAYMERGEELEMENTS Function

Contents:

Expand all Collapse all

Contents:

Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.

Basic Usage

Array literal reference example:

derive type:single value:ARRAYMERGEELEMENTS(["A","B","C","D"],"-")

Output: Generates a new column with the following String value in it: "A-B-C-D".

Column reference example:

derive type:single value:ARRAYMERGEELEMENTS([myValues) as:myValuesMergedTogether'

Output: Generates the new myValuesMergedTogether column containing all of the elements in the arrays in the myElement column joined together without a delimiter between them.

Syntax and Arguments

derive type:single value:ARRAYMERGEELEMENTS(array_ref,my_element, [string_delimiter])

Argument	Required?	Data Type	Description
array_ref	Y	array	Name of Array column, Array literal, or function returning an Array to apply to the function
string_delimiter	Y	string	Optional String delimiter to insert between merged elements in the output String.

For more information on syntax standards, see Language Documentation Syntax Notes.

array_ref

Name of the array column, array literal, or function whose elements you wish to merge.

Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	String (column reference or function) or array literal	`myArray1`

string_delimiter

Optional string value to insert between elements in the merged output string.

Usage Notes:

Required?	Data Type	Example Value
No	String	`"-"`

Examples

Tip: For additional examples, see Common Tasks.

Example - Podium Race Finishes

This example covers the following functions:

ARRAYSLICE - Returns an array that is a slice of another array, based on the provided starting and ending index numbers. See ARRAYSLICE Function.
ARRAYMERGEELEMENTS - Merges the elements of an array together into a string. See ARRAYMERGEELEMENTS Function.

Source:

The following set of arrays contain results, in order, of a series of races. From this list, the goal is to extract a list of the podium finishers for each race as a single string.

RaceId	RaceResults
1	["racer3","racer5","racer2","racer1","racer6"]
2	["racer6","racer4","racer2","racer1","racer3","racer5"]
3	["racer4","racer3","racer5","racer2","racer6","racer1"]
4	["racer1","racer2","racer3","racer5"]
5	["racer5","racer2","racer4","racer6","racer3"]

Transform:

From the list of arrays, the first step is to gather the top-3 finishers from each race:

derive type: single value: ARRAYSLICE(RaceResults, 0, 3) as: 'arrPodium'

The above captures the first three values of the RaceResults arrays into a new set of arrays.

The next step is to merge this new set of arrays into a single string:

derive type: single value: ARRAYMERGEELEMENTS(arrPodium, ',') as: 'strPodium'

Results:

RaceId	RaceResults	arrPodium	strPodium
1	["racer3","racer5","racer2","racer1","racer6"]	["racer3","racer5","racer2"]	racer3,racer5,racer2
2	["racer6","racer4","racer2","racer1","racer3","racer5"]	["racer6","racer4","racer2"]	racer6,racer4,racer2
3	["racer4","racer3","racer5","racer2","racer6","racer1"]	["racer4","racer3","racer5"]	racer4,racer3,racer5
4	["racer1","racer2","racer3","racer5"]	["racer1","racer2","racer3"]	racer1,racer2,racer3
5	["racer5","racer2","racer4","racer6","racer3"]	["racer5","racer2","racer4"]	racer5,racer2,racer4

See Also for ARRAYMERGEELEMENTS Function:

Send Feedback

Page tree