Analytics SAQL Developer Guide

Overview
Quick Start
Examples
SAQL Reference
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
Time Zone Date Functions
Work with Custom Fiscal Year Data
String Functions
ascii()
chr()
ends_with()
index_of()
len()
lower()
ltrim()
mv_to_string()
number_to_string
replace()
rtrim()
starts_with()
string_to_number
substr()
trim()
upper()
Math Functions
Windowing Functions
coalesce
Query Performance
PDF

Note: This release is in preview. Features described here don’t become generally available until the latest general availability date that Salesforce announces for this release. Before then, and where features are noted as beta, pilot, or developer preview, we can’t guarantee general availability within any particular time frame or at all. Make your purchase decisions only on the basis of generally available products and features.

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.

  1. From Setup, enter Analytics in the Quick Find box.
  2. Select Settings from the list of Analytics options.
  3. 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