Search
Clear search
Close search
Google apps
Main menu

CASE

Add flow control to a calculated field

The CASE statement lets you add logic-based flow control to a calculated field.

CASE evaluates to exactly one value based on the given boolean expressions. If multiple expressions evaluate to true, the value specified for the first true expression will be returned.

CASE supports a number of operators you can use to build complex logical statements. (These operators are only available within a CASE statement.) You can also use other functions, such as Math, Text, Date, etc. inside the CASE statement.

In this article:

Syntax

CASE
  WHEN X1 THEN Y1
  WHEN X2 THEN Y2
  WHEN Xn THEN Yn
  ELSE Y
END

Parameters:

  • Xn - an expression which evaluates to a boolean
  • Yn - text, number, or boolean

Example

Group country values into sales regions:

CASE WHEN REGEXP_MATCH(country, "(USA|Canada|Mexico)" )THEN "North America" WHEN REGEXP_MATCH(country, "(England|France)" ) THEN "Europe" Else "Other" END

Comparison operators

The following operators are supported: =, !=, >, >=, <, <=

The operands on both sides of the comparison operators must of the same type: text, number or boolean. At least one operand must be a field, the other one can be a text, number, or boolean.

CASE WHEN A=5 THEN 10 ELSE 20 END
CASE WHEN name='tony' THEN 'family' ELSE 'friend' END

Logical operators

The following operators are supported:

The operand on both side of logical operators must evaluate to a boolean type.

Examples

CASE WHEN (A=5 AND A>5) OR B=5 THEN 10 ELSE 20 END
CASE WHEN (A=5 OR B=10) THEN 100 ELSE 220 END

IS NULL operator

This expression returns true if the value of the field is of the null type.

Syntax

field IS NULL

Examples

middle_name IS NULL
ssn IS NULL

IN operator

This expression returns true if field is one of the the listed values. Both the field and list values must be of the same type.

Syntax

field NOT IN (Y1, Y2, ... YN)

Examples

name IN ('Bob', 'Alice') returns true if name is "Bob" or "Alice" 
number IN (10, 20) returns true if number is 10 or 20
name IN ('Bob', 20) returns an error, as 20 is not of the correct type (text)

NOT IN operator

This expression returns true if field is not one of the listed values. Both the field and list values must be of the same type.

Syntax

field IN (Y1, Y2, ... YN)

Examples

name NOT IN (‘Bob’, ‘Alice’) returns true if name is not "Bob" or "Alice" 
number NOT IN (10, 20) returns true if number is not 10 or 20
name NOT IN ('Bob', 20) returns an error, as 20 is not of the correct type (text)

Notes

  • Only dimension fields can be used in boolean expressions.
  • All values used in THEN and ELSE expressions must be of the same type: text, number, or boolean.
  • The CASE statement can return text, number, boolean values, or dimension fields. It cannot return metric fields.
Was this article helpful?
How can we improve it?
What's new in Data Studio

Learn about new features and recent changes.