mv_to_string()

Converts multivalue fields to string fields.

Syntax

mv_to_string(multivalue_column_name, delimeter)

Parameter	Description
multivalue_column_name	Name of the multivalue field to be converted to a string.
delimiter	Optional. The characters used to delimit values in the converted string. Maximum length is 2 characters.

Usage

Returns an alphabetically-sorted, delimited string representation of a multivalue field. The default delimiter is a comma followed by a space (, ).

mv_to_string() applies to non-grouped streams only. You can run filtering or grouping on a multivalue field post-projection.

To enable multivalue fields, you must select the Enable indexing of multivalue fields in Analytics preference in Setup. If you run mv_to_string() without the preference selected, the function returns the first value in the first field only.

From Setup, enter Analytics in the Quick Find box.
Select Settings from the list of Analytics options.
In Settings, click the checkbox for Enable indexing of multivalue fields in CRM Analytics.

Example

This query returns values of the Accounts Team as a string delimited by a comma and space, in alphabetical order.

1q = load "account;
2q = foreach q generate 'Name' as 'Account', mv_to_string('Account_Team') as 'Account Team';

Account	Account Team
Acme	Fred Williamson, Hank Chen, Sarah Vasquez
DTC Electronics	Brian Alison, Tessa McNaley
Salesforce	Nadia Smith

Example

This query returns the values of Accounts Team as a string delimited by two semicolons (;;) in alphabetical order.

1q = load "account";
2q = foreach q generate 'Name' as 'Account', mv_to_string('Account_Team', ";;") as 'Account Team';

Account	Account Team
Acme	Fred Williamson;;Hank Chen;;Sarah Vasquez
DTC Electronics	Brian Alison;;Tessa McNaley
Salesforce	Nadia Smith

See Also

Multivalue Field