There are times when we are required to do some manipulations on fields that are stored as strings. Typical calculations that we may do on strings are as follows:
In the following recipe, let's do some string manipulations to create new calculated fields.
For the following recipe, we will use the Customer Name field from the Orders sheet from Sample - Superstore.xlsx
data and we will continue working in the same workbook, My first Tableau Workbook
.
Let us get started by creating a fresh new sheet.
In the preceding recipe, we did two things. Firstly, we used the Split function in Tableau to extract part of the entire string to get a substring that gave us two new calculations called Customer First Name and Customer Last Name. Since this was a shortcut functionality that was quickly available, we used the same. However, the exact same formula that Tableau has auto-computed for us could also have been written from scratch in the Create Calculated Field… window.
Next, after we got the Customer First Name and Customer Last Name fields, we then concatenated the Customer First Name and Customer ID field using a + operator to create a single field called Concatenated string.
Now, the Customer ID field in our data is alphanumeric and Tableau is treating it as a String data type. However, imagine if the Customer ID field had numeric values, Tableau would treat it as an Integer data type. In that case, if we tried concatenating the Customer First Name and Customer ID fields using a + operator, Tableau would throw an error that would prohibit us from adding a String field (Customer First Name) and an Integer field (Customer ID).
In order to resolve this kind of data type mismatch issue, we can simply do a Type Conversion on Customer ID and convert it into String. We could use the STR()
function and our calculation would be updated as [Customer First Name]+STR([Customer ID]).
To understand the various String functions available in Tableau, please refer to http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_string.html.