KNOWLEDGE BASE

Concatenating a string value with an empty string results in NULL


Published: 28 Oct 2016
Last Modified Date: 20 Mar 2017

Issue

When connecting live to an Oracle data source and writing a calculation that concatenates an empty string "" with a value, then the result is NULL. If the same calculated field is remade or edited when the data source is extracted then the calculation returns the expected value.

Environment

  • Tableau Desktop
  • Oracle

Resolution

Option 1: Modify the calculation to not use empty strings

Option 2: Use an extract rather than a live connection

Option 3: Conditionally concatenate strings only if they are not empty. For example:

IF LEN( [String calculation 1] ) > 0
AND LEN( [String calculation 2] ) > 0
THEN [String calculation 1] + [String calculation 2]
ELSEIF LEN( [String calculation 1] ) > 0
THEN  [String calculation 1]
ELSE  [String calculation 2]
END

Cause

Oracle does not follow standard conventions for CONCAT operator with null values.
 

Additional Information

Note: when the extract is refreshed Tableau Desktop will query the Oracle data causing the calculated field to return NULL. Therefore after refreshing the extract, the calculated field will need to be remade.
Did this article resolve the issue?