xmetricFunctions

Tableau function Operators

Tableau OR to SQL

[p] OR [q] is true when either side is true. With a null side it is true if the other side is true, and null otherwise, exactly like SQL. The usual slip is in the IIF around it: a null test gives null in Tableau and ELSE in a CASE.

Updated · Checked on the xmetric engine

What is the syntax?

a OR b
a, b
booleans, or comparisons that return true, false or null.

Returns true, false or null (three-valued logic).

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 or q true?

Tableau

[p] OR [q]

SQL (DuckDB)

SELECT p OR q AS result
FROM input
ORDER BY id

Row 5 (null, true) is true; row 8 (false, null) is null.

pqResult
truetruetrue
truefalsetrue
falsetruetrue
falsefalsefalse
truetrue
null
truetrue
falsenull

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

Where does a straight translation go wrong?

Common mistake

Why does the CASE label row 8 "no"?

False OR null is null, and IIF returns null for a null test. CASE sends it to ELSE. Add a WHEN NOT (...) branch.

Tableau

IIF([p] OR [q], 'yes', 'no')

Tempting SQL

SELECT CASE WHEN p OR q THEN 'yes' ELSE 'no' END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE
    WHEN p OR q THEN 'yes'
    WHEN NOT (p OR q) THEN 'no'
    END AS result
FROM input
ORDER BY id
pqTableauTempting SQLSQL that matches
truetrueyesyesyes
truefalseyesyesyes
falsetrueyesyesyes
falsefalsenonono
trueyesyesyes
nullnonull
trueyesyesyes
falsenullnonull

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 p OR q AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT p OR q AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own OR 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 OR?

What is null OR true?

True: one true side is enough. Null OR false is null.

How do I test one field against several values?

Chain ORs, or use IN: [s] IN ('East', 'West').

Where is this documented?