Functions - Less Documentation

COALESCE

Returns the first non-null value from a list of expressions. Evaluates expressions from left to right and returns the first one that is not null. If all expressions are null, returns null.

Take first nulls_col and if that's null then take simple_int and if that's null then return 999

IFF

Conditional function/statement that returns one value if a condition is true, and another value if false. Can be nested within other IFF functions for complex conditional logic. Syntax: IFF(condition, value_if_true, value_if_false).

The condition must default to a boolean value (true or false). That means that the statement ‘“hello”’ is not a boolean value. However, [column]=“hello” is a boolean value because it is either true or false for a given row.

If nulls_col is larger than 40 then return 'larger than 40', if it is null then return 'null' and if none of those are true then return 'not larger than 40 and not null'

CONCAT

Concatenates two or more string values into a single string with a custom separator/delimiter. Combines multiple text values in the order they are provided. Returns an empty string if all input values are null.

Turning simple_int into a string and concatenating it with text_col and 'HELLOWORLD' separated by a hyphen

FLOAT

Converts a value to a decimal column type. Handles conversion from strings, integers, and other numeric types.

Converting a string to a float

Taking the right-most character from the text_col and converting it to a float - then dividing b 0.234

INT

Converts a value to an integer columns type. Rounds down to the nearest integer when converting from float values.

Taking the right-most character from the text_col and converting it to an integer

DATE

Converts a text literal or text column to a datetime type. Note that the value has to be without a time component and in this format: YYYY-MM-DD.

Converting a text literal - 2025-01-01 - to a date

DATETIME

Converts a text literal or text column to a datetime type. Note that the value has to be with a time component and in this format: YYYY-MM-DD HH:MM:SS.

Converting date_string column to a datetime

AND

Logical operator that returns true only if all conditions are true. Typically used within IFF functions or IF Column tool for complex conditional logic. Returns false if any condition is false or null.

Each part of the AND function must default to a boolean value (true or false). That means that the statement ‘“hello”’ is not a boolean value. However, [column]=“hello” is a boolean value because it is either true or false for a given row.

Using AND in IFF function. If nulls_col is null AND simple_int is 2 then return 'both are true' else return 'both are NOT true'

Logical operator that returns true if at least one condition is true. Typically used within IFF functions or IF Column tool for complex conditional logic. Returns false only if all conditions are false or null.

Each part of the OR function must default to a boolean value (true or false). That means that the statement ‘“hello”’ is not a boolean value. However, [column]=“hello” is a boolean value because it is either true or false for a given row.

Using OR in IFF function. If nulls_col is null OR simple_int is 2 then return 'one is true' else return 'none are true'

ISNULL

Returns a boolean value indicating whether a value is null. Returns true if the value is null, false otherwise. Useful for conditional logic and data validation.

Checking if nulls_col is null

ISNOTNULL

Returns a boolean value indicating whether a value is not null. Returns true if the value has any non-null content, false if the value is null. Useful for conditional logic and data validation.

Checking if nulls_col is not null

ISTRUE

Returns a boolean value indicating whether a value is true. Returns true if the value is true, false otherwise. Useful for conditional logic and data validation.

Created a column if bool is true

ISFALSE

Returns a boolean value indicating whether a value is false. Returns true if the value is false, false otherwise. Useful for conditional logic and data validation.

Created a column if bool is false

Canvas