Case(expression, value1, result1, value2, result2, …, valueN, resultN, else-result)
This function checks if an expression is equal to multiple values and returns the corresponding result; otherwise, the else-result is returned.
Parameters
- expression
- The column, expression, or literal value to test. Can be of any type.
- value
- The column, expression, or literal value to compare with the expression. All values should be of the same type as the expression.
- result
- The column, expression, or literal value. Must all be of the same type as result1.
- else-result
- The column, expression, or literal value. Must be of the same type as result1. If omitted, it is assumed to be null.
Returns
The return type is the same as result1.
Remarks
The Case function is designed to avoid duplication of an expression when it is to be checked against a series of values. The Case function may contain an unlimited number of value-result pairs. The expression is evaluated once and compared to each of the values sequentially. If the expression is equal to any value, the corresponding result is returned. If it is not equal to any of the values, the else-result is returned.
Examples
Case([Priority], "High", 100, "Medium", 70, "Low", 40)
- This formula says: if the value in the Priority column is "High", then return 100. If the value in the Priority column is "Medium", then return 70. If the value in the Priority column is "Low", then return 40. Otherwise, return nothing (null).Case([Status], "Overdue", "red", "Completed", "green")
- This formula says: if the value in the Status column is "Overdue", then color it red. If the value in the Status column is "Completed", then color it green. Otherwise, don’t apply any specific colorization.