Analytics SAQL Reference

Overview
Quick Start
Examples
SAQL Reference
SAQL Basic Elements
SAQL Operators
Arithmetic Operators
Comparison Operators
String Operators
Logical Operators
Simple case Operator
Searched case Operator
Null Operators
SAQL Statements
SAQL Functions
Query Performance
PDF

Newer Version Available

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

Searched case Operator

Use case in a foreach statement to assign different field values in different situations. case supports two syntax forms: searched case and simple case. This section shows searched case.

Syntax

case...end opens and closes the case operator.

primary_expr is any expression that takes a single input value and returns a single output value. May contain values, identifiers, and scalar functions (including date and math functions). The expression can return a number, string, or date.

when...then defines a conditional statement. A case expression can contain one or more conditional statements

test_expr is any expression that takes a single input value and returns a single output value. May contain values, identifiers, and scalar functions (including date and math functions). The expression must return the same data type as primary_expr.

result_expr is any expression that takes a single input value and returns a single output value. May contain values, identifiers, and scalar functions (including date and math functions). The expression must return the same data type as primary_expr.

else default_expr (optional) is any expression that takes a single input value and returns a single output value. May contain values, identifiers, and scalar functions (including date and math functions). The expression can return a number, string, or date.

Usage

Statements are evaluated in the order that they are given. If primary_expr = test_expr, the corresponding result_expr is returned. You can specify any number of when/then statements.

You can use else to specify a default expression. For example, if no industry is specified, you can use the string "No Industry Specified". If you don't specify a default statement then null is returned.

You can use case expressions in foreach statements. You cannot use case in order, group, or filter statements.

Example

Suppose that you want to see the median deal size for each of your reps. You want to bin their median deal size into the buckets "Small", "Medium", and "Large". Use case to assign values to the median deal size.

The resulting data shows the median deal size for each rep, along with the appropriate bin label.

Derived dimension shows time to win

Handling Null Values

In general, null values can’t be compared. When primary_expr or test_expr evaluates to null, the default_expr is returned. If no default expression is specified, null is returned.