Changes to the Language

The following changes have been applied to Wrangle in this release of Designer Cloud Powered by Trifacta Enterprise Edition.

Release 8.11

Nest transformation explicitly types transformed column

In prior releases, when a Nest transformation was applied to a column to nest values into Arrays or Objects, the resulting column was re-inferred by the Trifacta Application. This re-inference should not be necessary, since the target column's data type is effectively declared in the transformation definition.

Beginning in this release, the output column of these Nest transformations is explicitly typed to Array or Object data type, based on the definition of the transformation.

Note

Existing uses of the Nest transformation are not immediately affected. However, if these transformations are edited, then the changes may cause unexpected results and breakages in downstream transformations. If the recipe was originally designed expecting a different data type, subsequent steps may have been used to clean up the nested data, assuming that it was String values or some other data type. If the output column is now explicitly typed as Array or Object data type, these steps may be broken. You may be able to fix these broken steps by explicitly typing the output column to String after the Nest transformation and before your subsequent steps.

For more information, see Nest Your Data.

Release 8.10

Split by position no longer requires sorted list of positions

Beginning in this release, when you create a Split by position transformation, the numeric values indicating the positions do not need to be listed in sorted numeric order.

Tip

You can now do faster iteration since you can add new positions as needed when previewing the transformation.

For more information, see Split Column.

Release 8.5

Support for numeric separators in NUMFORMAT function

Beginning in Release 8.5, the NUMFORMAT function supports the following configurable separators for localizing numeric values:

Option Name	Description
Decimal Separator	The string used to separate the integer part of a Decimal value from its fractional part.
Grouping Separator	The string used to separate a group of digits.

For more information, see NUMFORMAT Function.

New functions

Function Name	Description
NUMVALUE Function	Converts a string formatted as a number into an Integer or Decimal value by parsing out the specified decimal and group separators. A string or a function returning formatted numbers of String type or a column containing formatted numbers of string type can be inputs.

Release 8.4

New functions

Documentation for the following functions is now available.

Function Name	Description
FINDNTH Function	Returns the position of the nth occurrence of a letter or pattern in the input string where a specified matching string is located in the provided column. You can search either from left or right.
PARSESTRING Function	Evaluates an input against the String datatype. If the input matches, the function outputs a String value. Input can be a literal, a column of values, or a function returning values. Values can be of any data type.
PARSEARRAY Function	Evaluates a String input against the Array datatype. If the input matches, the function outputs an Array value. Input can be a literal, a column of values, or a function returning String values.
PARSEOBJECT Function	Evaluates a String input against the Object datatype. If the input matches, the function outputs an Object value. Input can be a literal, a column of values, or a function returning String values.

Release 8.3

None.

Release 8.2

None.

Release 7.10

New functions

Function Name	Description
EOMONTH Function	Returns the serial date number for the last day of the month before or after a specified number of months from a starting date.

Release 7.9

Transform Builder now supports All columns option

Beginning in Release 7.9, select All columns option has been added in the Transform Builder.

Option Name	Description
All	Selects all columns in the dataset

Example:

Transformation Name	`Rename columns`
Parameter: Option	Add suffix
Parameter: Columns	All
Parameter: Suffix	_new

The following is the list of the transformations that accept the All option for selecting columns:

Date format
Delete columns
Move columns
Rename column
Replace
Replace text or patterns
Replace cells
Replace text between delimiters
Replace by position
Replace mismatched values
Replace missing values
Edit with formula
Change column type
Text format
Unpivot columns

For more information, see Transform Builder.

Release 7.8

Rename transform now supports Upper / Lower and Left / Right options

Beginning in Release 7.8, the Rename transform supports the following new options:

Option Name	Description
Convert to lowercase	Converts existing column names to lowercase
Convert to UPPERCASE	Converts existing column names to uppercase
Keep from beginning (left)	Specifies the number of characters to keep from the beginning of column names
Keep from end (right)	Specifies the number of characters to keep from the end of column names

For more information on rename columns, see Rename Columns.

Release 7.5

New Functions

Approximation statistical functions:

Tip

Approximation functions are suitable for larger datasets. As the number of rows increases, accuracy and calculation performance improves for these functions.

Function Name	Description
APPROXIMATEMEDIAN Function	Computes the approximate median from all row values in a column or group. Input column can be of Integer or Decimal.
APPROXIMATEPERCENTILE Function	Computes an approximation for a specified percentile across all row values in a column or group. Input column can be of Integer or Decimal.
APPROXIMATEQUARTILE Function	Computes an approximation for a specified quartile across all row values in a column or group. Input column can be of Integer or Decimal.

base64 encoding functions:

Function Name	Description
BASE64ENCODE Function	Converts an input value to base64 encoding with optional padding with an equals sign (`=`). Input can be of any type. Output type is String.
BASE64DECODE Function	Converts an input base64 value to text. Output type is String.

Release 7.4

New Functions

Function Name	Description
WEEKDAYNAME Function	Derives the full name from a Datetime value of the corresponding weekday as a String. Source value can be a reference to a column containing Datetime values or a literal.

Release 7.3

New Functions

Function Name	Description
ROLLINGMAXDATE Function	Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type.
ROLLINGMINDATE Function	Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type.
ROLLINGMODEDATE Function	Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values must be of Datetime data type.

Release 7.2

New Functions

Function Name	Description
KTHLARGESTDATE Function	Extracts the ranked Datetime value from the values in a column, where `k=1` returns the maximum value. The value for `k` must be between 1 and 1000, inclusive. Inputs must be valid Datetime values.
KTHLARGESTUNIQUEDATE Function	Extracts the ranked unique Datetime value from the values in a column, where `k=1` returns the maximum value. The value for `k` must be between 1 and 1000, inclusive. Inputs must be Datetime.
KTHLARGESTDATEIF Function	Extracts the ranked Datetime value from the values in a column, where `k=1` returns the maximum value, when a specified condition is met. The value for `k` must be between 1 and 1000, inclusive. Inputs must be Datetime.
KTHLARGESTUNIQUEDATEIF Function	Extracts the ranked unique Datetime value from the values in a column, where `k=1` returns the maximum value, when a specified condition is met. The value for `k` must be between 1 and 1000, inclusive. Inputs must be Datetime.
MINDATEIF Function	Returns the minimum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.
MAXDATEIF Function	Returns the maximum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.
MODEDATEIF Function	Returns the most common Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.

Release 7.1

New Functions

This release introduces the following functions to calculate the difference between two valid dates.

Date calculation functions:

Function Name	Description
MINDATE Function	Computes the minimum value found in all row values in a Datetime column.
MAXDATE Function	Computes the maximum value found in all row values in a Datetime column.
MODEDATE Function	Computes the most frequent (mode) value found in all row values in a Datetime column.

Work day functions:

Function Name	Description
NETWORKDAYS Function	Calculates the number of working days between two specified dates, assuming Monday - Friday workweek. Optional list of holidays can be specified.
NETWORKDAYSINTL Function	Calculates the number of working days between two specified dates. Optionally, you can specify which days of the week are working days as an input parameter. Optional list of holidays can be specified.
WORKDAY Function	Calculates the work date that is before or after a start date, as specified by a number of days. A set of holiday dates can be optionally specified.
WORKDAYINTL Function	Calculates the work date that is before or after a start date, as specified by a number of days. You can also specify which days of the week are working days and a list of holidays via parameters.

Time zone conversion functions:

Function Name	Description
CONVERTFROMUTC Function	Converts Datetime value to corresponding value of the specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.
CONVERTTOUTC Function	Converts Datetime value in specified time zone to corresponding value in UTC time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.
CONVERTTIMEZONE Function	Converts Datetime value in specified time zone to corresponding value second specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.

Release 6.11

New Functions

This release introduces the following functions to evaluate String values against individual data types.

Function Name	Description
PARSEBOOL Function	Evaluates a String input against the Boolean datatype. If the input matches, the function outputs a Boolean value. Input can be a literal, a column of values, or a function returning String values.
PARSEFLOAT Function	Evaluates a String input against the Decimal datatype. If the input matches, the function outputs a Decimal value. Input can be a literal, a column of values, or a function returning String values.
PARSEINT Function	Evaluates a String input against the Integer datatype. If the input matches, the function outputs an Integer value. Input can be a literal, a column of values, or a function returning String values.

PARSEDATE function now supports four default Datetime format values

Prior to Release 6.11, the PARSEDATE function required that you submit an array of Datetime formats as the second parameter of the function.

In Release 6.11, the PARSEDATE function supports the following default Datetime values:

'yyyy-MM-dd HH:mm:ss' 
'yyyy/MM/dd HH:mm:ss' 
'yyyy-MM-dd' 
'yyyy/MM/dd'

Note

These defaults are used only if the function reference does not contain a second parameter of an array of valid Datetime formats.

For more information, see PARSEDATE Function.

ignore case parameter added to string functions

In Release 6.11, the following functions now support an ignore case parameter. It is not required, and the default value is false. By default, matches are case-sensitive.

STARTSWITH Function
ENDSWITH Function
EXACT Function
MATCHES Function
STRINGGREATERTHAN Function
STRINGGREATERTHANEQUAL Function
STRINGLESSTHAN Function
STRINGLESSTHANEQUAL Function
SUBSTITUTE Function (additional details below)

Expanded parameters for SUBSTITUTE function

In Release 6.11, the accepted parameter inputs have been expanded.

Parameter Name	pre-Release 6.11 inputs	Release 6.11 or later inputs	Notes
string_source	String literal, column, or function returning String values	No change
string_pattern	String literal, pattern, or regex	String literal, pattern, or regex or a column or function returning String values
string_replacement	String literal	String literal or a column or function returning String values
ignore_case	n/a	If `true`, matching is case-insensitive. Default is `false`.	New parameter
pattern_before	String literal, pattern, or regex	No change	Not permitted when `string_pattern` or `string_replacement` is of column data type.
pattern_after	String literal, pattern, or regex	No change	Not permitted when `string_pattern` or `string_replacement` is of column data type.

For more information, see SUBSTITUTE Function.

Release 6.9

MODE functions return lowest value in evaluated set if there is a tie

Suppose you have the following set of values:

Price	Quantity
5.23	3
3	7
7.88	3
-3.12	-1
0	6
5.23	0
8.37	38

You apply the following transformation:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	MODEIF(Quantity, Price < 5)
Parameter: New column name	'modeif-test'

For the rows where price < 5, there is no most commonly occurring value in Quantity.

In Release 6.8 and earlier, the returned value was null.

In Release 6.9 and later, the returned value is the lowest value among the evaluated set. Among the qualifying rows, the lowest value in the Quantity column is -1.

New Functions

This release introduces the following statistical functions.

Function Name	Description
MEDIAN Function	Computes the median from all row values in a column or group. Input column can be of Integer or Decimal.
PERCENTILE Function	Computes a specified percentile across all row values in a column or group. Input column can be of Integer or Decimal.
QUARTILE Function	Computes a specified quartile across all row values in a column or group. Input column can be of Integer or Decimal.
CORREL Function	Computes the correlation coefficient between two columns. Source values can be of Integer or Decimal type.
COVAR Function	Computes the covariance between two columns using the population method. Source values can be of Integer or Decimal type.
COVARSAMP Function	Computes the covariance between two columns using the sample method. Source values can be of Integer or Decimal type.

Release 6.8

New Functions

This release introduces the sampling method of calculating statistical functions. The following are now available:

Function Name	Description
STDEVSAMP Function	Computes the standard deviation across column values of Integer or Decimal type using the sample statistical method.
VARSAMP Function	Computes the variance among all values in a column using the sample statistical method. Input column can be of Integer or Decimal. If no numeric values are detected in the input column, the function returns `0`.
STDEVSAMPIF Function	Generates the standard deviation of values by group in a column that meet a specific condition using the sample statistical method.
VARSAMPIF Function	Generates the variance of values by group in a column that meet a specific condition using the sample statistical method.
ROLLINGSTDEVSAMP Function	Computes the rolling standard deviation of values forward or backward of the current row within the specified column using the sample statistical method.
ROLLINGVARSAMP Function	Computes the rolling variance of values forward or backward of the current row within the specified column using the sample statistical method.

Release 6.6

New Functions

Function Name	Description
SINH Function	Computes the hyperbolic sine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
COSH Function	Computes the hyperbolic cosine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
TANH Function	Computes the hyperbolic tangent of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
ASINH Function	Computes the arcsine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
ACOSH Function	Computes the arccosine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
ATANH Function	Computes the arctangent of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.

Release 6.5

New Functions

Function Name	Description
SIN Function	Computes the sine of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
COS Function	Computes the cosine of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
TAN Function	Computes the tangent of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
Cotangent Function	See TAN Function.
Secant Function	See COS Function.
Cosecant Function	See SIN Function.
ASIN Function	For input values between -1 and 1 inclusive, this function returns the angle in radians whose sine value is the input. This function is the inverse of the sine function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
ACOS Function	For input values between -1 and 1 inclusive, this function returns the angle in radians whose cosine value is the input. This function is the inverse of the cosine function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
ATAN Function	For input values between -1 and 1 inclusive, this function returns the angle in radians whose tangent value is the input. This function is the inverse of the tangent function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
Arccotangent Function	See ATAN Function.
Arcsecant Function	See ACOS Function.
Arccosecant Function	See ASIN Function.

Release 6.4

Improvements to metadata references

Broader support for metadata references: For Excel files, $filepath references now return the location of the source Excel file. Sheet names are appended to the end of the reference. See Source Metadata References.

Release 6.3

New Functions

Function Name	Description
PARSEDATE Function	Evaluates an input against the default input formats or (if specified) an array of Datetime format strings in their listed order. If the input matches one of the formats, the function outputs a Datetime value.

Optional input formats for DateFormat task

The DateFormat task now supports a new parameter: Input Formats. This parameter specifies the date format to use when attempting to parse the input column.

If the parameter is specified, then the value of the parameter is used to parse the inputs.

(default) if the parameter is not specified, then the following common formats are used for parsing the input:

'M/d/yy',
'MM/dd/yy',
'MM-dd-yy',
'M-d-yy',
'MMM d, yyyy',
'MMMM d, yyyy',
'EEEE, MMMM d, yyyy',
'MMM d yyyy',
'MMMM d yyyy',
'MM-dd-yyyy',
'M-d-yyyy',
'yyyy-MM-ddXXX',
'dd/MM/yyyy',
'd/M/yyyy',
'MM/dd/yyyy',
'M/d/yyyy',
'yyyy/M/d',
'M/d/yy h:mm a',
'MM/dd/yy h:mm a',
'MM-dd-yy h:mm a',
'MMM dd yyyy HH.MM.SS xxx',
'M-d-yy h:mm a',
'MMM d, yyyy h:mm:ss a',
'EEEE, MMMM d, yyyy h:mm:ss a X',
'EEE MMM dd HH:mm:ss X yyyy',
'EEE, d MMM yyyy HH:mm:ss X',
'd MMM yyyy HH:mm:ss X',
'MM-dd-yyyy h:mm:ss a',
'M-d-yyyy h:mm:ss a',
'yyyy-MM-dd h:mm:ss a',
'yyyy-M-d h:mm:ss a',
'yyyy-MM-dd HH:mm:ss.S',
'dd/MM/yyyy h:mm:ss a',
'd/M/yyyy h:mm:ss a',
'MM/dd/yyyy h:mm:ss a',
'M/d/yyyy h:mm:ss a',
'MM/dd/yy h:mm:ss a',
'MM/dd/yy H:mm:ss',
'M/d/yy H:mm:ss',
'dd/MM/yyyy h:mm a',
'd/M/yyyy h:mm a',
'MM/dd/yyyy h:mm a',
'M/d/yyyy h:mm a',
'MM-dd-yy h:mm:ss a',
'M-d-yy h:mm:ss a',
'MM-dd-yyyy h:mm a',
'M-d-yyyy h:mm a',
'yyyy-MM-dd h:mm a',
'yyyy-M-d h:mm a',
'MMM.dd.yyyy',
'd/MMM/yyyy H:mm:ss X',
'dd/MMM/yy h:mm a',

These formats are a subset of the date formatting strings supported by the product. For more information, see Datetime Data Type.

Release 6.2

New Functions

Function Name	Description
RANK Function	Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by the number of tie values.
DENSERANK Function	Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by 1.

ARRAYELEMENTAT function accepts new inputs

In previous releases, the ARRAYELEMENTAT function accepted a second input parameter to specify the index value of the element to retrieve. This "at" parameter had to be an Integer literal.

Beginning in this release, the function also accepts for this second "at" parameter:

Names of columns containing Integer values
Functions that return Integer values

For more information, see ARRAYELEMENTAT Function.

Release 6.1

None.

Release 6.0

New Functions

Function Name	Description
ARRAYINDEXOF Function	Computes the index at which a specified element is first found within an array. Indexing is left to right.
ARRAYRIGHTINDEXOF Function	Computes the index at which a specified element is first found within an array, when searching right to left. Returned value is based on left-to-right indexing.
ARRAYSLICE Function	Returns an array containing a slice of the input array, as determined by starting and ending index parameters.
ARRAYMERGEELEMENTS Function	Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.

Changes to LIST* inputs

The following LIST-based functions have been changed to narrow the accepted input data types. In previous releases, any data type was accepted for input, which was not valid for most data types.

In Release 6.0 and later, these functions accept only Array inputs. Inputs can be Array literals, a column of Arrays, or a function returning Arrays.

Note

You should references to these functions in your recipes.

LIST* Functions
LISTAVERAGE Function
LISTMIN Function
LISTMAX Function
LISTMODE Function
LISTSTDEV Function
LISTSUM Function
LISTVAR Function

Renamed functions

The following functions have been renamed in Release 6.0.

Release 5.9 and earlier	Release 6.0 and later
LISTUNIQUE Function	UNIQUE Function

FILL Function has new before and after parameters

Prior to Release 6.0, the FILL function replaced empty cells with the most recent non-empty value.

In Release 6.0, before and after function parameters have been added. These parameters define the window of rows before and after the row being tested to search for non-empty values. Within this window, the most recent non-empty value is used.

The default values for these parameters are -1 and 0 respectively, which performs a search of an unlimited number of preceding rows for a non-empty value.

Note

Upon upgrade, the FILL function retains its preceding behavior, as the default values for the new parameters perform the same unlimited row search for non-empty values.

For more information, see FILL Function.

Release 5.9

New functions

The following functions can now be applied directly to arrays to derive meaningful statistics about them.

Function	Description
ARRAYSORT Function	Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.
TRANSLITERATE Function	Transliterates Asian script characters from one script form to another. The string can be specified as a column reference or a string literal.

Release 5.8

File lineage information using source metadata references

Beginning in Release 5.8, you can insert the following references into the formulas of your transformations. These source metadata references enable you to continue to track file lineage information from within your datasets as part of your wrangling project.

Note

These references apply only to file-based sources. Some additional limitations may apply.

reference	Description
$filepath	Returns the full path and filename of the source of the dataset.
$sourcerownumber	Returns the row number for the current row from the original source of the dataset. Note This reference is equivalent to the SOURCEROWNUMBER function, which is likely to be deprecated in a future release. You should begin using this reference in your recipes.

For more information, see Source Metadata References.

New math and statistical functions for arrays

The following functions can now be applied directly to arrays to derive meaningful statistics about them.

Function	Description
LISTSUM Function	Computes the sum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTMAX Function	Computes the maximum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTMIN Function	Computes the minimum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTAVERAGE Function	Computes the average of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTVAR Function	Computes the variance of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTSTDEV Function	Computes the standard deviation of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
LISTMODE Function	Computes the most common value of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.

Release 5.7

WEEKNUM function now behaves consistently across running environments

In Release 5.6 and earlier, the WEEKNUM function treated the first week of the year differently between the Trifacta Photon and Spark running environments:

Trifacta Photon week 1 of the year: The week that contains January 1.
Spark week 1 of the year: The week that contains at least four days in the specified year.

This issue was caused by Spark following an ISO-8601 standard and relying on the joda datetimeformatter.

Beginning in Release 5.7, the WEEKNUM function behaves consistently for both Trifacta Photon and Spark:

Week 1 of the year: The week that contains January 1.

For more information, see WEEKNUM Function.

Release 5.6

URLPARAMS function returns null values

In Release 5.1 and earlier, the URLPARAMS function returned empty Objects when no answer was computed for the function.

In Release 5.6 and later, this function returns null values in the above case.

See URLPARAMS Function.

Release 5.1

Wrangle now supports nested expressions

Beginning in Release 5.1, all Wrangle functions now supported nested expressions, which can be arithmetic calculations, column references, or other function calls.

Note

This feature is enabled by default, as this change does not break any steps created in previous versions of the product. It can be disabled if needed. See Miscellaneous Configuration.

Note

This capability represents a powerful enhancement to the language, as you can now use dynamic inputs for all functions.

The following expression is a valid transform in Wrangle. It locates the substring in myString that begins with the @ sign until the end of the string, inclusive:

derive value: substring(myString, find(myString, '@', true, 0), length(myString)

Nested arithmetic expressions:

Suppose you wanted just the value after the @ sign until the end of the string. Prior to Release 5.1, the following generated a validation error:

derive value: substring(myString, find(myString, '@', true, 0) + 1, length(myString)

In the above, the addition of +1 to the second parameter is a nested expression and was not supported. Instead, you had to use multiple steps to generate the string value.

Beginning in Release 5.1, the above single-step transform is supported.

Nested column references:

In addition to arithmetic expressions, you can nested column references. In the following example, the previous step has been modified to replace the static +1 with a reference to a column containing the appropriate value (at_sign_offset) :

derive value: substring(myString, find(myString, '@', true, 0) + at_sign_offset, length(myString)

Nested function references:

Now, you can combine multiple function references into a single computation. The following computes the total volume of a cube of length side and then multiplies that volume by the number of cubes (cube_count) to compute the total cube_volume

derive type: single value: MULTIPLY(POW(cube_side,3),cube_count) as: 'cube_volume'

For more information, see Wrangle Language.

SOURCEROWNUMBER function generates null values consistently

The SOURCEROWNUMBER function returns the row number of the row as it appears in the original dataset. After some operations, such as unions, joins, and aggregations, this row information is no longer available.

In Release 5.0.1 and earlier, the results were confusing. When source row information was not available, the function was simply not available for use.

In Release 5.1 and later, the behavior of the SOURCEROWNUMBER function is more consistent:

If the source row information is available, it is returned.
If it is not available:
- The function can still be used.
- The function returns null values in all cases.

For more information, see SOURCEROWNUMBER Function.

New Functions

Function Name	Description
ARRAYELEMENTAT Function	Returns element value of input array for the provided index value.
DOUBLEMETAPHONE Function	Returns primary and secondary phonetic spellings of an input string using the Double Metaphone algorithm.
DOUBLEMETAPHONEEQUALS Function	Returns `true` if two strings match phonetic spellings using Double Metaphone algorithm. Tolerance threshold can be adjusted.
UNIQUE Function	Generates a new column containing an array of the unique values from a source column.

Release 5.0.1

RAND function generates true random numbers

In Release 5.0 and earlier, the RAND function produced the same set of random numbers within the browser, after browser refresh, and over subsequent runs of a job.

During job execution, a default seed value was inserted as the basis for the function during the execution of the job.
In some cases, this behavior is desired.

In Release 5.0.1 and later, the RAND function accepts an optional integer as a parameter. When this new seed value is inserted, the function generates deterministic, pseudo-random values.

This version matches the behavior of the old function.
Note
On all upgraded instances of the platform, references to the RAND function have been converted to use a default seed value, so that previous behavior is maintained in the upgraded version.
If no seed value is inserted as a parameter, the RAND function generates true random values within the browser, after browser refresh, and over subsequent job runs.
Note
Be aware that modifying your dataset based on the generated values of RAND() may have unpredictable effects later in your recipe and downstream of it.

For more information, see RAND Function.

Release 5.0

Required type parameter

Prior to Release 5.0, the following was a valid Wrangle step:

derive value:colA + colB as:'colC'

Beginning in Release 5.0, the type parameter is required. This parameter defines whether the transform is a single or multi-row formula. In the Transform Builder, this value must be specified.

The following is valid in Release 5.0:

derive type:single value:colA + colB as:'colC'

See Derive Transform.

See Transform Builder.

Deprecated aggregate transform

In Release 4.2.1 and earlier, the aggregate transform could be used to aggregate your datasets using aggregation functions and groupings.

In Release 5.0 and later, this transform has been merged into the pivot transform. The aggregate transform has been deprecated and is no longer available.

Note

During upgrade to Release 5.0 and later, recipes that had previously used the aggregate transform are automatically migrated to use the pivot equivalent.

Example 1

Release 4.2.1 and earlier Aggregate:

aggregate value:AVERAGE(Scores)

Release 5.0 and later Pivot:

pivot value: AVERAGE(Score) limit: 1

The limit parameter defines the maximum number of columns that can be generated by the pivot.

Example 2

Aggregate:

aggregate value:AVERAGE(Scores) group:studentId

Pivot:

pivot group: StudentId value: AVERAGE(Score) limit: 1

For more information, see Pivot Transform.

New search terms

In the new Search panel, you can search for terms that can be used to select transformations for quick population of parameters. In the following table, you can see Wrangle how terminology has changed in Release 5.0 for some common transforms from earlier release.

Tip

You can paste the Release 5.0 terms in the Search panel to locate the same transformations used in earlier releases.

Release 4.2.1 and earlier transforms	Release 5.0 and later search terms
aggregate	pivot
keep	filter
delete	filter
extract on:	extractpatterns
extract at:	extractpositions
extract before:	extractbetweendelimiters
extract after:	extractbetweendelimiters
replace on:	replacepatterns
replace at:	replacepositions
replace before:	replacebetweenpatterns
replace after:	replacebetweenpatterns
replace from:	replacebetweenpatterns
replace to:	replacebetweenpatterns
split on:	splitpatterns
split delimiters:	splitpositions
split every:	splitpositions
split positions:	splitpositions
split after:	splitpatterns
split before:	splitpatterns
split from:	splitpatterns
split to:	splitpatterns

Support for <> operator

Prior to Release 5.0, the following operator was used to test "not equal" comparisons:

!=

Beginning in Release 5.0, the following operators is also supported:

<>

Example:

derive value:IF ((col1 <> col2), 'different','equal') as:'testNotEqual'

Tip

Both of the above operators are supported, although the <> operator is preferred.

For more information, see Comparison Operators.

ROUND function takes optional number of digits

The ROUND function now supports rounding to a specified number of digits. By default, values are rounded to the nearest integer, as before. See ROUND Function.

New Functions

Function Name	Description
DEGREES Function	Generates the value in degrees for an input radians value.
EXACT Function	Compares two strings to see if they are exact matches.
FILTEROBJECT Function	Filters the keys and values from an Object based on specified keys.
HOST Function	Returns the host value from a URL.
ISEVEN Function	Returns `true` if an Integer, function returning an Integer, or a column contains an even value.
ISODD Function	Returns `true` if an Integer, function returning an Integer, or a column contains an odd value.
KTHLARGESTUNIQUE Function	Computes the kth-ranked unique value in a set of values.
LCM Function	Returns the least common multiple between two input values.
MODE Function	Computes the mode (most common) value for a set of values.
MODEIF Function	Computes the mode based on a conditional test.
PAD Function	Pads the left or right side of a value with a specified character string.
PI Function	Generates the value for pi to 15 decimal places.
RADIANS Function	Generates the value in radians for an input degrees value.
RANDBETWEEN Function	Generates a random Integer in a range between two specified values.
RIGHTFIND Function	Locates a substring by searching from the right side of an input value.
ROLLINGCOUNTA Function	Computes count of non-null values across a rolling window within a column.
ROLLINGKTHLARGEST Function	Computes the kth largest value across a rolling window within a column.
ROLLINGKTHLARGESTUNIQUE Function	Computes the kth largest unique value across a rolling window within a column.
ROLLINGLIST Function	Computes list of all values across a rolling window within a column.
ROLLINGMAX Function	Computes maximum value across a rolling window within a column.
ROLLINGMIN Function	Computes minimum value across a rolling window within a column.
ROLLINGMODE Function	Computes mode (most common) value across a rolling window within a column.
ROLLINGSTDEV Function	Computes standard deviation across a rolling window within a column.
ROLLINGVAR Function	Computes variance across a rolling window within a column.
SIGN Function	Computes the positive or negative sign of an input value.
TRUNC Function	Truncates a value to the nearest integer or a specified number of digits.
URLPARAMS Function	Extracts any query parameters from a URL into an Object.
WEEKNUM Function	Calculates the week that the date appears during the year (1-52).

Release 4.2.1

None.

Release 4.2

New Filter transform

Perform a variety of predefined row filtrations using the new filter transform, or apply your own custom formula to keep or delete rows from your dataset.

See Remove Data.
See Filter Transform.

New Case transform

Beginning in Release 4.2, you can use the Transform Builder to simplify the construction of CASE statements. For each case, specify the conditional and resulting expression in separate textboxes.

See Apply Conditional Transformations.
See Case Transform.

Rename transform now supports multi-column rename

Use the rename transform to rename multiple columns in a single transform.

See Rename Columns.
See Rename Transform.

Delete specified columns or delete the others

The drop transform now supports the option of deleting all columns except the ones specified in the transform. See Drop Transform.

New string comparison functions

Compare two strings using Latin collation settings. See below.

NOW function returns 24-hour time values

In Release 4.1.1 and earlier, the NOW function returned time values for the specified time zone in 12-hour time, which was confusing.

In Release 4.2 and later, this function returns values in 24-hour time.

New Transforms

Transform Name	Documentation
case	Case Transform
filter	Filter Transform

New Functions

Function Name	Documentation
STRINGGREATERTHAN	STRINGGREATERTHAN Function
STRINGGREATERTHANEQUAL	STRINGGREATERTHANEQUAL Function
STRINGLESSTHAN	STRINGLESSTHAN Function
STRINGLESSTHANEQUAL	STRINGLESSTHANEQUAL Function
SUBSTITUTE	SUBSTITUTE Function

In this section: