1. String and Logical functions
Welcome back.
In this video, we will examine the most frequently used string and logical functions.
2. Overview of data types
But first, let’s have a look at data types.
Data type is an attribute of a piece of data that tells a computer how to interpret its value.
Some examples are shown here.
Tableau designates various data types with icons, as shown in the table on the right.
In structured data sources, such as relational databases, only one type of data is stored in a data field.
The data type determines what calculations can be done on that field.
For example, it is not possible to perform some operations on dates, such as multiplication or division.
3. String data type
Let’s zoom in on string data type.
A string is a sequence of one or more letters, numbers, or other characters, including spaces.
As you can see from the examples, a string can store alphanumeric values, of varying lengths.
In calculations, string values are enclosed in double quotation marks.
4. String functions in Tableau
Let’s have a look at some useful string functions in Tableau!
Concatenation is, in other words, joining two or more strings end-to-end. For example, combining, First and Last name into one Full name column.
In Tableau, we do it in a calculated field using a plus and a space character enclosed in a double quotation.
Another useful function is the Split function. It extracts parts of a string based on a delimiter (in other words, a dividing character).
For example, we split the Name field, but we only retain the first component before the comma, in this case, the Last name.
5. String functions in Tableau
LEFT and RIGHT functions extract the requested fixed number of characters from the beginning or the end of the string, and MID function can do the same but from the specified position inside the string.
6. Logical functions: Boolean (True/False)
Let’s proceed to logical functions.
This family of functions determines if a condition is true or false and returns a requested value based on the evaluation of the condition.
Let’s first have a look at Boolean conditions, which always return either a True or False.
Remember our Top Athlete calculation from previous exercises? It was a Boolean condition.
You can include several checks in the same condition, adding AND OR statements and including brackets in the right places.
Lastly, a negation can either be written as NOT, with exclamation and equal sign, or with two opposed V brackets.
7. Logical functions: IF, IIF, CASE
Next up is the very popular IF function. You typically use an IF function to perform a sequence of arbitrary tests which can include algebra conditions such as less than, greater or equal to, etc.
Notice that the syntax differs from the one of popular spreadsheet program Excel as in Tableau, unlike in Excel, IF function comprises words THEN and END. It can be expanded with ELSE and ELSEIF if more evaluations are needed.
The simplified version of the IF function is called IIF. Its syntax is very similar to the IF function in Excel.
The Case function resembles the IF function, but there is a critical difference: it only searches for a match to an expression and cannot work with e.g., algebra conditions.
Case can always be rewritten as an IF function but not vice versa.
8. Other logical functions
Tableau has many useful logical functions so let’s have a look at a few more.
ISNULL and ISDATE are simple Boolean evaluators, returning a True or a false as a result.
IFNULL function does the same as ISNULL but allows us to control the output. For example, if Steps are null, return zero or return the “No steps” string.
Lastly, a ZN() function is a handy shortcut of IFNULL that always returns zero if a value is null.
9. Let's practice!
Now onto you. Let’s practice!