Wrangle Language

A reference to the values stored in a column in your dataset. Columns can be referenced by the plain-text value for the column name.

Example: value parameter references the myCol column.

Single row formula

myCol

'myNewCol'

Column names with spaces or special characters in a transformation must be wrapped by curly braces.

Example: Below, srcColumn is renamed to src Column, which requires no braces because the new name is captured as a string literal:

Manual rename

srcColumn

src Column

Some parameters accept functions as inputs. Where values or formulas are calculated, you can reference one of the dozens of functions available in Wrangle.

Example:

Single row formula

MULTIPLY(3,2)

'six'

Wrangle supports the use of variable references to aspects of the source data or dataset. In the following example, the ABS function is applied to each column in a set of them using the $col reference.

val1,val2

ABS($col)

$col returns the value from the current column for the row under evaluation.For more information on these variables, see Source Metadata References.

Individual parameters within a function can be computed expressions themselves. These nested expressions can be calculated using constants, other functions, and column references.

Example: Computes a column whose only value is ten divided by three, rounded to the nearest integer (3):

Single row formula

ROUND(DIVIDE(10,3),0)

'three'

A valid integer value within the accepted range of values for the Integer datatype.

Example: Generates a column called, my13 which is the sum of the Integer values 5 and 8:

Single row formula

(5 + 8)

'my13'

A valid floating point value within the accepted range of values for the Decimal datatype.

Example: Generates a column of values that computes the approximate circumference of the values in the diameter column:

Single row formula

(3.14159 * diameter)

'circumference'

A true or false value.

Example: If the value in the order column is more than 1,000,000, then the value in the bigOrder column is true.

Single row formula

IF(order > 1000000, true, false)

'bigOrder'

A string literal value is the baseline datatype. String literals must be enclosed in single quotes.

Example: Creates a column called, StringCol containing the value myString.

Single row formula

'myString'

'StringCol'

Designer Cloud Powered by Trifacta Enterprise Edition supports a special syntax, which simplifies the generation of matching patterns for string values.

Patterns must be enclosed in accent marks ( `MyPattern`). For more information onWrangle, seeText Matching.Example: Extracts up to 10 values from the MyData column that match the basic pattern for social security numbers (XXX-XX-XXXX):

MyData

Custom text or pattern

`%{3}-%{2}-%{4}`

10

Regular expressions are a common standard for defining matching patterns. Regex is a very powerful tool but can be easily misconfigured.

Regular expressions must be enclosed in slashes (/MyPattern/).

Example: Deletes all two-digit numbers from the qty column:

qty

/^\d$|^\d\d$/

''

true

A valid date or time value that matches the requirements of the Datetime datatype.

Datetime values can be formatted with specific formatting strings. See DATEFORMAT Function.

Example: Generates a new column containing the values from the myDate column reformatted in yyyymmdd format:

Single row formula

DATEFORMAT(myDate, 'yyyymmdd')

A valid array of values matching the Array data type.

Example:

[0,1,2,3,4,5,6,7,8]

Example: Generates a column with the number of elements in the listed array (7):

Single row formula

ARRAYLEN('["red", "orange", "yellow", "green", "blue", "indigo", "violet"]')

A valid set of values matching the Object data type.

Example:

{"brand":"Subaru","model":"Impreza","color","green"}

Example: Generates separate columns for each of the specified keys in the object ( brand, model, color), containing the corresponding value for each row:

myCol

'brand','model','color'

For more information on these data types, see Supported Data Types.