xmetricFunctions

Tableau function Operators

Tableau < (less) to SQL

[a] < [b] is true when a is smaller and null when either side is missing, as in SQL. For text, a Tableau extract compares without case, so West < west is false; DuckDB orders capitals first and says true.

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
Westwestfalse
EastNorthtrue
NorthNorthfalse
southEastfalse
Eastnull
Westnull
EastSouthtrue
easteastfalse

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

Where does a straight translation go wrong?

Common mistake

Why does s < t put West before west?

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 id

SQL that matches

SELECT lower(s) < lower(t) AS result
FROM input
ORDER BY id
stTableauTempting SQLSQL that matches
Westwestfalsetruefalse
EastNorthtruetruetrue
NorthNorthfalsefalsefalse
southEastfalsefalsefalse
Eastnullnullnull
Westnullnullnull
EastSouthtruetruetrue
easteastfalsefalsefalse

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.

Does < work on dates?

Yes: [e] < [d] is true when e is earlier.

Where is this documented?