Page tree

Trifacta Dataprep


Contents:

On April 28, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.

   

Contents:


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

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Array literal reference example:

arraymergeelements(["A","B","C","D"],"-")


Output:
 Returns the following String value: "A-B-C-D"

Column reference example:

arraymergeelements([myValues)


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

arraymergeelements(array_ref,my_element, [string_delimiter])


ArgumentRequired?Data TypeDescription
array_refYarrayName of Array column, Array literal, or function returning an Array to apply to the function
string_delimiterYstringOptional 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 TypeExample Value
YesString (column reference or function) or array literalmyArray1

string_delimiter

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

Usage Notes:

 

Required?Data TypeExample Value
NoString"-"

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.

RaceIdRaceResults
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"]

Transformation:

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

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYSLICE(RaceResults, 0, 3)
Parameter: New column name '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:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYMERGEELEMENTS(arrPodium, ',')
Parameter: New column name 'strPodium'

Results:

RaceIdRaceResultsarrPodiumstrPodium
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


This page has no comments.