xmetricFunctions

Tableau function Logical

Tableau IF to SQL

IF test THEN value [ELSEIF ...] [ELSE default] END returns the value of the first true test. A null test is not true, so a row with a missing value goes to ELSE, or to null when there is no ELSE. SQL's CASE WHEN test THEN value ELSE default END does exactly the same in DuckDB, Snowflake and BigQuery. Keep the test the way Tableau has it: flipping it moves the nulls.

Updated · Checked on the xmetric engine

What is the syntax?

IF <test> THEN <then> [ELSEIF <test2> THEN <then2> ...] [ELSE <default>]
END
test
a boolean expression.
then
the value when the test is true.
default
optional, the value when no test is true. A null test counts as not true.

Returns the THEN value of the first true test, the ELSE value, or null if no test is true and there is no ELSE.

What does it return, and what is the SQL?

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

Which scores pass?

Tableau

IF [Score] >= 80 THEN 'Pass' ELSE 'Retake' END

SQL (DuckDB)

SELECT
  CASE WHEN Score >= 80 THEN 'Pass' ELSE 'Retake' END AS result
FROM input
ORDER BY id

The missing score on row 4 is not true, so it gets Retake.

ScoreResult
92Pass
80Pass
50Retake
Retake
45Retake
80Pass

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

Where does a straight translation go wrong?

Common mistake

Why does flipping the test pass the missing score?

Rewriting >= 80 THEN Pass as < 80 THEN Retake ELSE Pass looks equivalent, but row 4's null test fails both ways and lands in the other ELSE, Pass.

Tableau

IF [Score] >= 80 THEN 'Pass' ELSE 'Retake' END

Tempting SQL

SELECT
  CASE WHEN Score < 80 THEN 'Retake' ELSE 'Pass' END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE WHEN Score >= 80 THEN 'Pass' ELSE 'Retake' END AS result
FROM input
ORDER BY id
ScoreTableauTempting SQLSQL that matches
92PassPassPass
80PassPassPass
50RetakeRetakeRetake
RetakePassRetake
45RetakeRetakeRetake
80PassPassPass

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

What does IF return without ELSE?

Rows where no test is true come back null. Columns 1 to 6 are the sample rows, by id.

CaseTableau, then SQL (DuckDB)123456
no ELSEIF [Score] >= 80 THEN 'Pass' ENDCASE WHEN Score >= 80 THEN 'Pass' ENDPassPassnullnullnullPass

On every row, each Tableau formula and its SQL return the same value.

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
  CASE WHEN Score >= 80 THEN 'Pass' ELSE 'Retake' END AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT
  CASE WHEN Score >= 80 THEN 'Pass' ELSE 'Retake' END AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own IF formula return?

Edit the formula and run it on the 6 sample rows. Fields: [id], [Region], [Season], [Score], [Flag], [Backup].

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

Which functions go with it?

What do people ask about IF?

Where does a null go in IF?

To ELSE: row 4 has no score, so [Score] >= 80 is null and the row gets Retake. IIF is different: it returns null.

What does IF return without ELSE?

Null for every row where no test is true, including rows where the test is null.

Can THEN and ELSE return different types?

No. Both branches must be the same type; wrap a number in STR() to mix it with text.

Where is this documented?