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

Newer Version Available

This content describes an older version of this product. View Latest

mv_to_string()

Converts multivalue fields to string fields.

Syntax

mv_to_string(multivalue_column_name, delimeter)
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.

Note

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