Expressions
Advanced Expressions

Logic in Expressions

Summary

  • AdaptableQL supports Logic in Expressions - this comes in 2 formats:
    • IF - uses a ternary operator
    • CASE statements (designed when using multiple clauses)

Expressions can also include custom Logic.

This can be provided in 2 ways:

  • via ternary logic (essentially an IF function)
  • using CASE Statements

IF Function

Expressions can include conditional logic via the IF function.

This wraps a ternary operator (which uses the ? sign) and is used for writing conditional expressions.

It is used in Logical Expressions and is made up of 3 parts

  1. a Boolean Expression to evaluate followed by a question mark (?)
  2. the return value if the expression is true, followed by a colon (:)
  3. the return value if the Expression is false
 // Return 'Big' if more than 100 Comments, otherwise 'Small'
 [Comments] > 100 ? 'Big' : 'Small'

There is no limit on the number of Ternary Operators in an Expression - which enables multiple conditions:

 // Return 'Big' if more than 100 Comments, 'Medium' if more than 50, otherwise 'Small'
 [Comments] > 100 ? 'Big' : [Comments] > 50 ? 'Medium' : 'Small'
Expression Logic: IF Function
Fork
  • This demo contains a Calculated Column which is evaluated using Ternary Logic
  • Popularity examines the number of stars in Github Stars and returns an appropriate string value using the following logic:
    • Greater than 100,000 - "Very Popular"
    • Greater than 30,000 - "Popular"
    • Others - "Trending"

CASE Statements

Sometimes the logic required in an Expression will make the Ternary Operator feel verbose or unsuitable.

For this reason, AdapTableQL also offers switch-type logic common in many languages.

It is provided by CASE statements which take this form:

CASE [case_value]
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END

As can be seen they are made up of (up to) 4 parts:

  1. CASE keyword followed - optionally - by the value being evaluated
  2. Any number of conditions which each take the form WHEN x THEN y
  3. A final, optional, ELSE to return the value if no conditions are met
  4. An END statement which wraps up the section
 CASE [day] WHEN 'Saturday' THEN 'weekend' WHEN 'Sunday' THEN 'weekend' ELSE 'workday' END

As noted, the initial evaluation after CASE is optional and can be omitted:

 CASE WHEN [price] < 10 THEN 'low' WHEN [price] < 50 THEN 'medium' ELSE 'high' END
Expression Logic: Case Statements
Fork
  • This demo contains 2 Calculated Columns which are evaluated using Case Statements
  • Popularity examines the number of stars in Github Stars and returns an appropriate string value using the following logic:
    • Greater than 100,000 - "Very High"
    • Greater than 40,000 - "Quite High"
    • Greater than 20,000 - "Low"
    • Else - "None"
  • File returns the correct file type for the Language column using the following logic:
    • JavaScript - ".js"
    • TypeScript - ".ts"
    • Else - ".html"

Conditional Logic in Expressions

(Recorded with AdapTable v14.0)
PreviousVAR FunctionNextFIELD Function