CASE

Description

CASE

is a row function that evaluates each row in the dataset according to one or more input conditions, and outputs the specified result when the input conditions are met.

Syntax

CASE

WHEN

input_condition

[

AND

|

OR

input_condition

]

THEN

output_expression

[...] [

ELSE

other_output_expression

]

END

Return Value

Returns one value per row of the same type as the output expression. All output expressions must return the same field type.

If there are multiple output expressions that return different field types, then you will need to enclose your entire

CASE

expression in one of the field type conversion functions, such as

TO_INT

, to explicitly cast all output values to a particular field type.

Input Parameters

WHEN input_condition: Required. The
WHEN
keyword is used to specify one or more Boolean expressions (see the supported conditional operators). If an input value meets the condition, then the output expression is applied. Input conditions can include other row functions in their expression, but cannot contain summarization functions or measure expressions. You can use the
AND
or
OR
keywords to combine multiple input conditions.
THEN output_expression: Required. The
THEN
keyword is used to specify an output expression when the specified conditions are met. Output expressions can include other row functions in their expression, but cannot contain summarization functions or measure expressions.
ELSE other_output_expression: Optional. The
ELSE
keyword can be used to specify an alternate output expression to use when the specified conditions are not met. If an
ELSE
expression is not supplied,
ELSE NULL
is the default.
END: Required. Denotes the end of
CASE
function processing.

Examples

Convert values in the age
column into range-based groupings (binning):

CASE WHEN [age] <= 25 THEN "0-25" WHEN [age] <= 50 THEN "26-50" ELSE "over 50" END

Transform values in the gender
column from one string to another:

CASE WHEN [gender] = "M" THEN "Male" WHEN [gender] = "F" THEN "Female" ELSE "Unknown" END

The vehicle
column contains the following values: truck, bus, car, scooter, wagon, bike, tricycle, and motorcycle. The following example converts multiple values in the vehicle
column into a single value:

CASE WHEN [vehicle] in ("bike","scooter","motorcycle") THEN "two-wheelers" ELSE "other" END