xmetricFunctions

Tableau function Logical

Tableau ELSEIF to SQL

ELSEIF test THEN value adds another test to an IF ... END chain. Tableau checks the tests in order and returns the value of the first true one, so later tests only see rows the earlier ones let through. In SQL each ELSEIF becomes another WHEN in a searched CASE, in the same order. Rewriting the chain as standalone ranges changes the boundaries.

Updated · Checked on the xmetric engine

What is the syntax?

IF <test1> THEN <then1>
ELSEIF <test2> THEN <then2> [...] [ELSE <default>]
END
test
a boolean expression, checked only if every earlier test was false or null.
then
the value returned when its test is the first true one.

Returns the THEN value of the first true test, else the ELSE value, else null.

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 grade does each score get?

Tableau

IF [Score] >= 80 THEN 'A'
ELSEIF [Score] >= 50 THEN 'B'
ELSE 'C'
END

SQL (DuckDB)

SELECT
  CASE
    WHEN Score >= 80 THEN 'A'
    WHEN Score >= 50 THEN 'B'
    ELSE 'C'
    END AS result
FROM input
ORDER BY id

80 is A because the first test already holds; 50 is B; the missing score and 45 fall to C.

ScoreResult
92A
80A
50B
C
45C
80A

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

What happens when the first test is null?

Tableau

IF [Flag] THEN 'flagged'
ELSEIF [Score] >= 80 THEN 'high'
ELSE 'other'
END

SQL (DuckDB)

SELECT
  CASE
    WHEN Flag THEN 'flagged'
    WHEN Score >= 80 THEN 'high'
    ELSE 'other'
    END AS result
FROM input
ORDER BY id

Row 6 has no flag, so the chain moves on and its score of 80 makes it high.

ScoreFlagResult
92trueflagged
80falsehigh
50other
trueflagged
45falseother
80high

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

Where does a straight translation go wrong?

Common mistake

Why does an 80 get B when the chain is rewritten with BETWEEN?

Standalone ranges overlap or leave gaps at the boundaries. BETWEEN 50 AND 80 includes 80, and checked first it grades rows 2 and 6 as B. In the ELSEIF chain, >= 80 comes first and wins.

Tableau

IF [Score] >= 80 THEN 'A'
ELSEIF [Score] >= 50 THEN 'B'
ELSE 'C'
END

Tempting SQL

SELECT
  CASE
    WHEN Score BETWEEN 50 AND 80 THEN 'B'
    WHEN Score > 80 THEN 'A'
    ELSE 'C'
    END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE
    WHEN Score >= 80 THEN 'A'
    WHEN Score >= 50 THEN 'B'
    ELSE 'C'
    END AS result
FROM input
ORDER BY id
ScoreTableauTempting SQLSQL that matches
92AAA
80ABA
50BBB
CCC
45CCC
80ABA

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

Snowflake documentation

BigQuery

SELECT
  CASE
    WHEN Score >= 80 THEN 'A'
    WHEN Score >= 50 THEN 'B'
    ELSE 'C'
    END AS result
FROM input
ORDER BY id

BigQuery documentation

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

Does the order of ELSEIF tests matter?

Yes. The first true test wins, so >= 50 before >= 80 would grade 92 as B. Keep the SQL WHENs in the same order.

What happens to a null in an ELSEIF chain?

A null test counts as not true, and the row moves on to the next test. Row 4 has no score and ends in ELSE, C.

Is ELSEIF the same as ELSE IF?

No. Tableau writes it as one word; ELSE IF would need its own END.

Where is this documented?