Multivalue Functions for Formulas

In CRM Analytics, use multivalue functions with your multivalue data. For example, look for a specific value, count how many values there are, create a new multivalue by combining other text, convert a multivalue to a string, or split a string and return a multivalue. The arguments for multivalue functions must be text strings or fields.

When you enter a formula, use the API name of the column in the expression, not the label. The parameters section is surrounded by parentheses. Also, text strings must be enclosed in single straight quotes ('This is a string.').

You can use the following multivalue functions in a formula expression.

Multivalue Function Description Syntax

Multivalue Function	Description	Syntax
`split`	Splits a string at the specified delimiter and returns a multivalue. If `limit` is specified, then returns at most the number of specified number of splits. Input: text, text Output: multivalue	`split(field,delimiter, limit(optional))` `field` is the text (dimension) field or string to be converted to a multivalue. `delimiter` is the value to separate the elements in the string. It can be a field or a text string. `limit` is the maximum number of elements returned in the multivalue. This parameter if optional. It can be a field or a number.
`array_join`	Converts the multivalue to a string with elements separated by the specified delimiter. If no string is specified for `nullReplacement`, nulls are ignored. Input: multivalue, text Output: text	`array_join(mvField,delimiter,nullReplacement(optional))` `mvField` is the multivalue text column to be converted to a string. `delimiter` is the value to separate the elements in the string. It can be a field or a text string. `nullReplacement` is the value that replaces the nulls. This parameter is optional. It can be a field or a text string.
`array_contains`	Returns TRUE if the specified string is found in the multivalue. Input: multivalue, text Output: text (true/false)	`array_contains(mvField,literal)` `mvField` is the multivalue field or column to be searched. `literal` is the value to search for at the beginning of the field. It can be a field or a text string.
`size`	Returns the number of elements in the multivalue. If the multivalue is null, returns `-1`. Input: multivalue Output: number	`size(mvField)` `mvField` is the multivalue text field to measure. When filtering out multivalue fields with both empty values and null values, to get consistent results between interactive preview and batch run results, use `size(mvField)<1` and not `size(mvField)=-1`
`array`	Returns a multivalue containing unique elements. Input: text Output: multivalue	`array(field1,field2,...fieldN)` `field1` is the first text column or string to include in the concatenated value. `field2` is the second text (dimension) column or string to include. `fieldN` is any number of extra text (dimension) columns or strings to include.
`element_at`	Returns the element of a multivalue at the given index. Input: multivalue, integer Output: text	`element_at(mvField, index)` `mvField` is the multivalue field to return the element from. `index` is the index to use in the array.
`array_distinct`	Removes duplicates from the multivalue. Input: multivalue Output: multivalue	`array_distinct(mvField)` `mvField` is the multivalue field to remove duplicates from.

split

Splits a string at the specified delimiter and returns a multivalue. If limit is specified, then returns at most the number of specified number of splits.

Input: text, text
Output: multivalue

split(field,delimiter, limit(optional))

field is the text (dimension) field or string to be converted to a multivalue.

delimiter is the value to separate the elements in the string. It can be a field or a text string.

limit is the maximum number of elements returned in the multivalue. This parameter if optional. It can be a field or a number.

array_join

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

Input: multivalue, text
Output: text

array_join(mvField,delimiter,nullReplacement(optional))

mvField is the multivalue text column to be converted to a string.

delimiter is the value to separate the elements in the string. It can be a field or a text string.

nullReplacement is the value that replaces the nulls. This parameter is optional. It can be a field or a text string.

array_contains

Returns TRUE if the specified string is found in the multivalue.

Input: multivalue, text
Output: text (true/false)

array_contains(mvField,literal)

mvField is the multivalue field or column to be searched.

literal is the value to search for at the beginning of the field. It can be a field or a text string.

size

Returns the number of elements in the multivalue. If the multivalue is null, returns -1.

Input: multivalue
Output: number

size(mvField)

mvField is the multivalue text field to measure.

When filtering out multivalue fields with both empty values and null values, to get consistent results between interactive preview and batch run results, use size(mvField)<1 and not size(mvField)=-1

array

Returns a multivalue containing unique elements.

Input: text
Output: multivalue

array(field1,field2,...fieldN)

field1 is the first text column or string to include in the concatenated value.

field2 is the second text (dimension) column or string to include.

fieldN is any number of extra text (dimension) columns or strings to include.

element_at

Returns the element of a multivalue at the given index.

Input: multivalue, integer
Output: text

element_at(mvField, index)

mvField is the multivalue field to return the element from.

index is the index to use in the array.

array_distinct

Removes duplicates from the multivalue.

Input: multivalue
Output: multivalue

array_distinct(mvField)

mvField is the multivalue field to remove duplicates from.