1. Docs
  2. Derived Column Formula Reference
  3. Operators and Functions
  4. Conditional Operators

Derived Column Conditional Operators

Conditional operators evaluate a condition that is applied to boolean expressions.

IF 

The IF statement takes two or more arguments. Every pair of arguments is evaluated as a condition; the final argument is the default: IF( condition, then-val [, condition2, then-val2]... [, else-val]).

  • If condition evaluates to true, evaluates to then-val.
  • If condition2 is specified, then if it is true, evaluates to then-val2.
  • If else-val is not specified, evaluates to null.
  • Otherwise evaluates to else-val.

All non-zero numbers, as well as the string true, evaluate to true.

# Usage: `IF( condition, then-val [, condition2, then-val2]... [, else-val])`
# Examples
IF(CONTAINS($team_name, "acme"), "important-customer", "everyone else")
IF(GTE($http_status, 400), 1, 0)
IF(GTE($duration_ms, 500), "slow")

# The multiple-value form can be used as a type of case statement,
# but SWITCH is more efficient if the same expression is being tested for strict equality.
IF(EQUALS($path,"/login"),"login",
   CONTAINS($path,"/browse/"),"browsing",
   "other"
  )

Note that the multi-argument IF is equivalent to nested IF statements:

# nested IF
IF(condition, value,
  IF(condition2, value2,
    IF(condition3, value3, default)
  )
)

# equivalent with multicondition IF
IF(condition, value,
   condition2, value2,
   condition3, value3, default)

SWITCH 

The SWITCH statement takes three or more arguments. The first argument is the value to test the cases against. Every pair of arguments is evaluated as a case and the value to return. If there is an unpaired final argument, it will be returned as the default if none of the cases match. If no default value exists, then null will be returned. SWITCH( expression, case1, val1[, case2, val2]... [, default-val]).

  • If expression equals case1, evaluates to val1.
  • If case2 is specified and equals expression, evaluates to val2.
  • If default-val is not specified, evaluates to null.
  • Otherwise evaluates to default-val.

The rules for EQUALS apply for testing cases.

# Usage: `SWITCH( expression, case1, val1[, case2, val2]... [, default-val])`
# Examples
SWITCH(REG_VALUE($service.name, "^([a-z]+)-shard-"),
  "alpha", "blue-owls",
  "beta", "violet-octopi",
  "delta", "violet-octopi",
  "gamma", "orange",
  "platform"
)

COALESCE 

Evaluates to the first non-empty argument. This is useful for similar fields where fallback values are preferable to null.

# Usage: COALESCE(arg1, arg2, ...)
# Examples
COALESCE($full_name, $email)
COALESCE($container_id, $hostname, $availability_zone, "unknown")
COALESCE($service.name, $service_name, "unknown")

# This can be used to approximate a case statement. For some cases, this is easier to write than the multi-factor IF statement.
COALESCE(
  IF(GTE($duration_ms, 200), "slow"),
  IF(GTE($status, 500), $error_message),
  IF(CONTAINS($team, "acme"), "high priority"),
  "normal"
)
On this page: