Page tree

Trifacta Dataprep


Contents:

On January 27, 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.

 

 

Generates an array of all unique elements among one or more arrays.
  • Inputs are column names or array literals.
  • If an element appears twice in one or more arrays, it is listed once in the output array.

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:

arrayunique([["A","B"],["A","C"]])


Output:
 Returns a single array:

["A","B","C"] 

Single-column reference example:


arrayunique([array1])

Output: Returns a single array of all unique elements in array1 .

Multi-column reference example:

arrayunique([array1,array2])

Output: Returns a single array listing all unique elements in array1 and array2 .

Syntax and Arguments

arrayunique(array_ref1,array_ref2)


ArgumentRequired?Data TypeDescription
array_ref1Ystring or arrayName of first column or first array literal to apply to the function
array_ref2Nstring or arrayName of second column or second array literal to apply to the function

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

array_ref1, array_ref2

Array literals or names of the array columns whose unique elements you want to derive.

Usage Notes:

Required?Data TypeExample Value
Yes (at least one)Array literal or column referencemyArray1, myArray2

Examples

Tip: For additional examples, see Common Tasks.

Example - Simple unique example

This simple example illustrates how the following functions operate on nested data.

Source:

Code formatting has been applied to improve legibility.

ItemArrayAArrayB
Item1["A","B","C"]["1","2","3"]
Item2["A","B"]["A","B","C"]
Item3["D","E","F"]["4","5","6"]

Transformation:

You can apply the following transforms in the following order. Note that the column names must be different from the transform name, which is a reserved word.

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYCONCAT([Letters,Numerals])
Parameter: New column name 'concat2'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYINTERSECT([Letters,Numerals])
Parameter: New column name 'intersection2'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYCROSS([Letters,Numerals])
Parameter: New column name 'cross2'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYUNIQUE([Letters,Numerals])
Parameter: New column name 'unique2'

Results:

For display purposes, the results table has been broken down into three separate sets of columns.

Column set 1:

ItemArrayAArrayBconcat2intersection2
Item1

["A","B","C"]

 

["1","2","3"]["A","B","C","1","2","3"][ ] 
Item2["A","B"]["A","B","C"]["A","B","A","B","C"]["A","B"]
Item3["D","E","F"]["4","5","6"]["D","E","F","4","5","6"][ ]

Column set 2:

Itemcross2
Item1[["A","1"],["A","2"],["A","3"],["B","1"],["B","2"],["B","3"],["C","1"],["C","2"],["C","3"]]
Item2[["A","A"],["A","B"],["A","C"],["B","A"],["B","B"],["B","C"]]
Item3[["D","4"],["D","5"],["D","6"],["E","4"],["E","5"],["E","6"], ["F","4"],["F","5"],["F","6"]]

Column set 3:

Itemunique2
Item1["A","B","C","1","2","3"]
Item2["A","B","C"]
Item3["D","E","F","4","5","6"]

This page has no comments.