Tableau function Operators
[a] != [b] is true when the values differ, false when they're equal and null when either is missing. DuckDB's != agrees on numbers. Two traps: IS DISTINCT FROM turns the nulls into true, and text compares case sensitively in DuckDB but not in a Tableau extract.
a != ba, bReturns true, false, or null when either side is null.
Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.
a != b?Tableau
[a] != [b]SQL (DuckDB)
SELECT a != b AS result
FROM input
ORDER BY idEvery complete pair differs; rows 6 and 7 are null.
| a | b | Result |
|---|---|---|
| 7 | 2 | true |
| −7 | 2 | true |
| 2 | 5 | true |
| 9 | 4 | true |
| 5 | 3 | true |
| 2 | null | |
| 1 | null | |
| −3 | −2 | true |
The Tableau formula and the SQL return the same 8 rows.
!= compare two text fields?Tableau
[s] != [t]SQL (DuckDB)
SELECT lower(s) != lower(t) AS result
FROM input
ORDER BY idCase is ignored, so row 1 (West, west) compares as equal text.
| s | t | Result |
|---|---|---|
| West | west | false |
| East | North | true |
| North | North | false |
| south | East | true |
| East | null | |
| West | null | |
| East | South | true |
| east | east | false |
The Tableau formula and the SQL return the same 8 rows.
Common mistake
IS DISTINCT FROM count missing values as different?It treats null as a value, so null versus 2 is true. Tableau returns null, and a filter on the result drops those rows.
Tableau
[a] != [b]Tempting SQL
SELECT a IS DISTINCT
FROM b AS result
FROM input
ORDER BY idSQL that matches
SELECT a != b AS result
FROM input
ORDER BY id| a | b | Tableau | Tempting SQL | SQL that matches |
|---|---|---|---|---|
| 7 | 2 | true | true | true |
| −7 | 2 | true | true | true |
| 2 | 5 | true | true | true |
| 9 | 4 | true | true | true |
| 5 | 3 | true | true | true |
| 2 | null | true | null | |
| 1 | null | true | null | |
| −3 | −2 | true | true | true |
The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.
Common mistake
s != t say West and west differ?Tableau's default collation on extracts and files ignores case, so West and west (row 1) compare as equal. DuckDB compares the bytes. Lower both sides.
Tableau
[s] != [t]Tempting SQL
SELECT s != t AS result
FROM input
ORDER BY idSQL that matches
SELECT lower(s) != lower(t) AS result
FROM input
ORDER BY id| s | t | Tableau | Tempting SQL | SQL that matches |
|---|---|---|---|---|
| West | west | false | true | false |
| East | North | true | true | true |
| North | North | false | false | false |
| south | East | true | true | true |
| East | null | null | null | |
| West | null | null | null | |
| East | South | true | true | true |
| east | east | false | false | false |
The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.
The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.
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.
The comparison returns null, not false, in Tableau and in SQL. A filter drops those rows, and IIF returns null for them.
On an extract or a file source, no: Tableau's default string collation ignores case, so West and west compare as equal. On a live connection the database's collation decides. DuckDB compares case sensitively, so lower both sides to match an extract.
Yes; see <>.