xmetricFunctions

Tableau function Operators

Tableau NOT to SQL

NOT [p] turns true into false and false into true; null stays null, as in SQL. NOT binds looser than comparisons, so NOT [a] > 5 means NOT ([a] > 5). Translating it as p IS NOT TRUE or coalesce(NOT p, true) counts missing values as true.

Updated · Checked on the xmetric engine

What is the syntax?

NOT a
a
a boolean or a comparison.

Returns true, false or null: the opposite of a.

What does it return, and what is the SQL?

Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

Which rows have p false?

Tableau

NOT [p]

SQL (DuckDB)

SELECT NOT p AS result
FROM input
ORDER BY id

Rows 5 and 6 have no p, so the result is null.

pResult
truefalse
truefalse
falsetrue
falsetrue
null
null
truefalse
falsetrue

The Tableau formula and the SQL return the same 8 rows.

How does NOT read with a comparison?

Tableau

NOT [a] > 5

SQL (DuckDB)

SELECT NOT a > 5 AS result
FROM input
ORDER BY id

It negates the whole comparison: row 1 (7 > 5) becomes false.

aResult
7false
−7true
2true
9false
5true
null
1true
−3true

The Tableau formula and the SQL return the same 8 rows.

Where does a straight translation go wrong?

Common mistake

Why does p IS NOT TRUE return true for missing flags?

IS NOT TRUE treats null as not true. Tableau keeps it null, so the rows drop out of a filter and an IIF.

Tableau

NOT [p]

Tempting SQL

SELECT p IS NOT TRUE AS result
FROM input
ORDER BY id

SQL that matches

SELECT NOT p AS result
FROM input
ORDER BY id
pTableauTempting SQLSQL that matches
truefalsefalsefalse
truefalsefalsefalse
falsetruetruetrue
falsetruetruetrue
nulltruenull
nulltruenull
truefalsefalsefalse
falsetruetruetrue

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT NOT p AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT NOT p AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own NOT formula return?

Edit the formula and run it on the 8 sample rows. Fields: [id], [a], [b], [x], [p], [q], [s], [t], [d], [e].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about NOT?

Does NOT [p] include rows where p is null?

No: NOT null is null, so a filter on NOT [p] drops them. Use NOT [p] OR ISNULL([p]) to keep them.

How does NOT combine with AND?

NOT binds tighter than AND: NOT [p] AND [q] is (NOT [p]) AND [q].

Where is this documented?