xmetricFunctions

Tableau function Operators

Tableau + (string) to SQL

[s] + [t] joins two strings, and any null part makes the whole result null. DuckDB's || behaves the same. concat() does not: it skips null parts and still returns text.

Updated · Checked on the xmetric engine

What is the syntax?

string + string
string
strings or string fields; numbers must go through STR() first.

Returns the joined string. Null if any part 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.

How do I join s and t with a separator?

Tableau

[s] + ' / ' + [t]

SQL (DuckDB)

SELECT s || ' / ' || t AS result
FROM input
ORDER BY id

Rows 5 and 6 miss one side, so the result is null.

stResult
WestwestWest / west
EastNorthEast / North
NorthNorthNorth / North
southEastsouth / East
Eastnull
Westnull
EastSouthEast / South
easteasteast / east

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

How do I put a number in a label?

Tableau

'#' + STR([a])

SQL (DuckDB)

SELECT '#' || CAST (a AS VARCHAR) AS result
FROM input
ORDER BY id

#7, #-7, and null where a is missing.

aResult
7#7
−7#-7
2#2
9#9
5#5
null
1#1
−3#-3

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

Where does a straight translation go wrong?

Common mistake

Why does concat() return text for rows 5 and 6?

concat() treats null as an empty string. Tableau returns null when any part is missing, so labels and counts differ.

Tableau

[s] + ' / ' + [t]

Tempting SQL

SELECT concat(s, ' / ', t) AS result
FROM input
ORDER BY id

SQL that matches

SELECT s || ' / ' || t AS result
FROM input
ORDER BY id
stTableauTempting SQLSQL that matches
WestwestWest / westWest / westWest / west
EastNorthEast / NorthEast / NorthEast / North
NorthNorthNorth / NorthNorth / NorthNorth / North
southEastsouth / Eastsouth / Eastsouth / East
Eastnull / Eastnull
WestnullWest / null
EastSouthEast / SouthEast / SouthEast / South
easteasteast / easteast / easteast / east

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 s || ' / ' || t AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT CONCAT(s, ' / ', t) AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own + (string) 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 + (string)?

How do I join a number to text?

Convert it first: '#' + STR([a]). In SQL, '#' || CAST(a AS VARCHAR).

How do I skip a missing part?

Wrap it: [s] + ' / ' + IFNULL([t], ''). That is when concat() is the right SQL.

Where is this documented?