xmetricFunctions

Tableau function Operators

Tableau AND to SQL

[p] AND [q] is true only when both are true. With a null side it is false if the other side is false, and null otherwise: the same three-valued logic as SQL. Inside IIF, the null rows return null, while a SQL CASE sends them to ELSE.

Updated · Checked on the xmetric engine

What is the syntax?

a AND 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 both p and q true?

Tableau

[p] AND [q]

SQL (DuckDB)

SELECT p AND q AS result
FROM input
ORDER BY id

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

pqResult
truetruetrue
truefalsefalse
falsetruefalse
falsefalsefalse
truenull
null
truenull
falsefalse

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 null rows "no"?

IIF returns null when its test is null; CASE WHEN ... ELSE puts those rows in ELSE. Add a WHEN NOT (...) branch.

Tableau

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

Tempting SQL

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

SQL that matches

SELECT
  CASE
    WHEN p AND q THEN 'yes'
    WHEN NOT (p AND q) THEN 'no'
    END AS result
FROM input
ORDER BY id
pqTableauTempting SQLSQL that matches
truetrueyesyesyes
truefalsenonono
falsetruenonono
falsefalsenonono
truenullnonull
nullnonull
truenullnonull
falsenonono

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

Snowflake documentation

BigQuery

SELECT p AND q AS result
FROM input
ORDER BY id

BigQuery documentation

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

What is null AND false?

False: one false side is enough. Null AND true is null.

Does AND short-circuit?

The result is the same either way; don't rely on the evaluation order for side effects.

Where is this documented?