Analytics SAQL Developer Guide

Overview
Quick Start
Examples
SAQL Reference
SAQL Basic Elements
Statements
Keywords
Identifiers
Number Literals
String Literals
Boolean Literals
Multivalue Field
Quoted String Escape Sequences
Special Characters
Comments
SAQL Operators
SAQL Statements
SAQL Functions
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.

Multivalue Field

A multivalue field contains more than one value.

Example

One typical use case for multivalue fields is security. For example, you can have a dataset that contains various accounts, and each account has multiple owner IDs. We’ve created a sample dataset called OppRoles where OwnerId is a multivalue field.

Account ID Amount Owner.Name Opportunity ID Owner ID Stage
001R00000046CHdIAM 1,900,013 Emily Dickinson 006R0000002OF6eIAG 005R0000000VU9bIAG, 005R0000000VU9bIAH, 005R0000000VU9bIAI Closed Won
001R00000046CV6IAM 70,449 Albert Einstein 006R0000002OF6eIAG 005R0000000VU9UIAW, 005R0000000VU9bIAG, 005R0000000VU9UIAX 005R0000000VU9UIAY Closed Won
001R00000046CI6IAM 4,206,995 Indiana Jones 006R0000002OF6eIAG 006R0000002OF6gIAG, 005R0000000VU9RIAW, 005R0000000VU9SIAW Closed Won

This query filters on an OwnerId to display only the accounts that it can access.

1q = load "OppRoles";
2q = filter q by 'OwnerId' in ["005R0000000VU9bIAG"];
3q = foreach q generate 'AccountId' as 'AccountId', 'Amount' as 'Amount', 'Id' as 'Id', 'Owner.Name' as 'Owner.Name', 'OwnerId' as 'OwnerId', 'StageName' as 'StageName';

When using comparison operators in the filter, use in and not in to return the correct values. Using == and != returns unexpected values when null handling is enabled. See Group-by with Null Values for more information.

Warning

Account ID Amount Owner.Name Opportunity ID Owner ID Stage
001R00000046CHdIAM 1,900,013 Emily Dickinson 006R0000002OF6eIAG 005R0000000VU9bIAG Closed Won
001R00000046CV6IAM 70,449 Albert Einstein 006R0000002OF6eIAG 005R0000000VU9bIAG Closed Won
The OwnerID value 005R0000000VU9bIAG has access to two of the three accounts, so two of the accounts are displayed.

Limit multivalue field use to filtering only. Multivalue fields can behave unpredictably with group and foreach.

Important