Search
Clear search
Close search

# 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.

## 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.