Multivalue Functions for Formulas

Multivalue data is limited to use in formulas. To work with multivalue data in downstream processes from batch data transforms in Data 360, such as reporting in CRM Analytics, convert it into a string.

Note Formula steps in transforms cannot directly output arrays. Therefore, functions that produce arrays, such as 'sequence', must be combined with functions that process arrays and return non-array results, such as 'explode' or 'array_join'.

`array_join`

Converts multivalue data into a string with elements separated by the specified delimiter. If no string is specified for nullReplacement, nulls are ignored.

Syntax

array_join(mvField,delimiter,nullReplacement(optional))

Parameters

mvField—(Required) the multivalue text column to be converted to a string.
delimiter—(Required) the value to separate the elements in the string. This can be a field or a text string.
nullReplacement—(Optional) he value that replaces the nulls. This can be a field or a text string.

Returns

A string that contains the multivalue data separated by the specified delimiter.

`explode`

Converts an array into a new column where each element is a row.

Syntax

explode(array)

Parameters

array—A field or formula expression.

Example

explode(sequence(dateField1, dateField2, interval 6 months))

Returns

Returns one row for each element in the array. The output includes a new field that has the value of each element in the array.

`sort_array`

Sorts the input array in ascending or descending order according to the natural ordering of the array elements. Null elements will be placed at the beginning of the returned array in ascending order or at the end of the returned array in descending order.

Syntax

sort_array(array,ascendingOder)

Parameters

array—(Required) is the array to be sorted..
ascendingOrder—(Required) sorts the elements as strings in ascending order.

Returns

The reference to the same array, now sorted.