Analytics SAQL Developer Guide
jmv_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 |