User GuidesAPI ReferenceRelease NotesEnrichment APIs
Doc HomeHelp CenterLog In

Using Logical Comparators

Logical comparators are expressions that compare values and return true or false.

There are are many ways to compare data using transformations. Logical comparators are most often used in JOIN, LOOKUP, and FILTER statements and in case expressions.

Syntax

Data Types

Example

Explanation

IS NULL

Any

state IS NULL

Returns true when state is a true null value.

IS NOT NULL

Any

state IS NOT NULL

Returns true when state is anything other than a true null value.

IS EMPTY

Any

state IS EMPTY

Returns true when state is a true null value, a blank string (a string with no content '' or a string containing only whitespace characters like spaces and tabs ' '), an empty array ([]), or an array containing only blank strings and true nulls ([' ', null])

IS NOT EMPTY

Any

state IS NOT EMPTY

Returns true when state is anything other than a true null value, a blank string (a string with no content '' or a string containing only whitespace characters like spaces and tabs ' '), an empty array ([]), or an array containing only blank strings and true nulls ([' ', null])

==

Any

state == 'MA'

Returns true when state is equal to 'MA'

=

Any

state = 'MA'

Returns true when state is equal to 'MA'

!=

Any

state != 'MA'

Returns true when state is not equal to 'MA'

>

Any Numeric

cost > 10.0

Returns true when cost is greater than 10.0

>=

Any Numeric

cost >= 10.0

Returns true when cost is greater than or equal to 10.0

<

Any Numeric

cost < 10.0

Returns true when cost is less than 10.0

<=

Any Numeric

cost <= 10.0

Returns true when cost is less than or equal to 10.0

You can modify a logical comparator by using NOT to reverse its logic. Logical comparators can also be combined with AND and OR.

Tip: To provide additional flexibility, functions are also available for several of these logical comparators. For example, ISNULL and IS_NOT_EMPTY.

Examples of Using Logical Comparators

Keep (that is, filter down to) rows that have a populated company_name or have a populated parent_company_name:

FILTER company_name IS NOT EMPTY OR parent_company_name IS NOT EMPTY;

Look up the exchange rate of a currency for a given year by matching on exact values in the year and currency columns.

LOOKUP exchange_rate
FROM "currency_exchange.csv" AS currency_lookup
ON year == currency_lookup.year AND currency == currency_lookup.currency;

Create a new attribute price_in_words and set it to 'expensive' when the price is greater than 100. Otherwise, set it to 'cheap':

SELECT *,
CASE 
WHEN price > 100 THEN 'expensive' 
ELSE 'cheap' 
END AS price_category;