IF

Syntax

As a block

As a function

IF condition_1
    THEN result_1
[ ELSEIF condition_2
    THEN result_2
  ... ]
ELSE
    default_result
END

IF(
    condition_1, result_1,
  [ condition_2, result_2,
    ... ]
    default_result
)

Description

Checks conditional expressions condition_1, result_1, ... and returns the matching result for the first condition found to be TRUE. IF all conditional expressions are FALSE, it returns default_result.

Argument types:

condition_1 — Any
result_1 — Any
condition_2 — Any
result_2 — Any
default_result — Any

Return type: Depends on argument types

Examples

IF
    [Profit] > 100
        THEN "High"
    ELSEIF [Profit] > 25
        THEN "Medium"
    ELSE "Low"
END

Example with data table

Formulas:

sales: [sales] ;
if_function: IF(ZN([sales]) < 100, "Less than 100", [sales] < 1000, "100 - 1000", "1000 and greater") ;
if_statement: IF ZN([sales]) < 100 THEN "Less than 100" ELSEIF [sales] < 1000 THEN "100 - 1000" ELSE "1000 and greater" END .

sales	if_function	if_statement
`432.40`	`'100 - 1000'`	`'100 - 1000'`
`77.00`	`'Less than 100'`	`'Less than 100'`
`12000.00`	`'1000 and greater'`	`'1000 and greater'`
`NULL`	`'Less than 100'`	`'Less than 100'`
`34.25`	`'Less than 100'`	`'Less than 100'`
`128.00`	`'100 - 1000'`	`'100 - 1000'`
`0.00`	`'Less than 100'`	`'Less than 100'`
`NULL`	`'Less than 100'`	`'Less than 100'`

Data source support

ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.

CASE

IFNULL