xmetricFunctions

Tableau function Operators

Tableau <= (less equal) to SQL

[a] <= [b] is true when a is smaller or equal, null when either side is missing, the same as SQL's <=. When it feeds an IIF, remember that IIF returns null for a null test, while CASE falls through to ELSE.

Updated · Checked on the xmetric engine

What is the syntax?

a <= b
a, b
two values of the same kind: numbers, strings, dates or booleans.

Returns true, false, or null when either side is null.

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 pass a <= b?

Tableau

[a] <= [b]

SQL (DuckDB)

SELECT a <= b AS result
FROM input
ORDER BY id

Rows 2, 3 and 8 pass; rows 6 and 7 are null.

abResult
72false
−72true
25true
94false
53false
2null
1null
−3−2true

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

How does <= compare two text fields?

Tableau

[s] <= [t]

SQL (DuckDB)

SELECT lower(s) <= lower(t) AS result
FROM input
ORDER BY id

Case is ignored, so row 1 (West, west) compares as equal text.

stResult
Westwesttrue
EastNorthtrue
NorthNorthtrue
southEastfalse
Eastnull
Westnull
EastSouthtrue
easteasttrue

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

Where does a straight translation go wrong?

Common mistake

Why does the CASE return "no" where Tableau returns null?

IIF returns null when its test is null (row 6 has no a, row 7 no b). A SQL CASE sends a null test to ELSE. Add a WHEN NOT branch so nulls fall through to null.

Tableau

IIF([a] <= [b], 'yes', 'no')

Tempting SQL

SELECT CASE WHEN a <= b THEN 'yes' ELSE 'no' END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE
    WHEN a <= b THEN 'yes'
    WHEN NOT (a <= b) THEN 'no'
    END AS result
FROM input
ORDER BY id
abTableauTempting SQLSQL that matches
72nonono
−72yesyesyes
25yesyesyes
94nonono
53nonono
2nullnonull
1nullnonull
−3−2yesyesyes

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 LOWER(s) <= LOWER(t) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT LOWER(s) <= LOWER(t) AS result
FROM input
ORDER BY id

BigQuery documentation

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

What happens when one side is null?

The comparison returns null, not false, in Tableau and in SQL. A filter drops those rows, and IIF returns null for them.

Is the string comparison case sensitive?

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.

How do I keep rows where b is missing?

Decide what a missing b means: [a] <= IFNULL([b], 0); in SQL, a <= coalesce(b, 0).

Where is this documented?