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:
- Concatenating separate strings into a single string or separate a string into smaller substrings
- Changing the letter case (upper case to lower case and vice versa)
- Extracting parts of the string to find certain substrings or find the position of a particular character or trim the string from a particular point
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.
- Let us create a new sheet by pressing Ctrl + M and rename it String Calculation.
- We will then drag the Customer Name field from the Dimensions pane and drop it into the Rows shelf. We will get a long list of 793 Customers, as shown in the following screenshot:
- The Customer Name field returns a name per Customer. Next, let us right-click on the Customer Name field in the Dimensions pane and select the Transform | Split option. This action will result in two new Dimensions being created called Customer Name - Split 1 and Customer Name - Split 2. Refer to the following screenshot:
- These two new calculations are actually the substring of the original field and if we pull these new fields in the view, we will see that Customer Name - Split 1 is giving us First Name of the Customer and Customer Name - Split 2 is giving us Last Name of the Customer. Thus, as a next step, we will rename Customer Name - Split 1 as Customer First Name and Customer Name - Split 2 as Customer Last Name by clicking on the dropdown or right clicking on each of the new fields in the Dimension shelf and selecting the Rename option. Also, if we look carefully, both these new fields are shown with =Abc as a prefix icon, which indicates that these are computed fields in Tableau and are of the string datatype. In order to understand what logic is being used to generate these fields, we will click on the dropdown or right click on these fields in the Dimensions pane and select the Edit… option. Once we do that, we will get to see the formula used by Tableau. Refer to the following screenshot:
- What Tableau did when we used the Split function, is that it auto-computed two calculated fields for us. Now, let us see how we can use these new calculations. Let us drag the Customer First Name field by dragging it from the Dimensions pane and dropping it into the Rows shelf. Make sure to place it before the Customer Name field, which is already present in the Rows shelf. Refer to the following screenshot:
- When we do that, we get to see that there are three customers with the first name Aaron, three customers with the first name Adam, and so on.
- Next, let us remove the Customer Name field from the Rows shelf. When we do that, we will notice that the number of rows now reduces from 793 to 338. Check the status bar. Refer to the following screenshot:
- This is because Tableau will always give us unique records. Once we remove the Customer Name field, first names such as Aaron and Adam become unique.
- Next, let us extend this recipe to further understand how to concatenate Strings. Let us drag the Customer ID field from the Dimensions pane and drop it into the Rows shelf, right next to the Customer First Name field. We will now see the number of rows increases to 793. This is because each Customer ID is unique to each Customer Name.
- As a next step, we will now concatenate the Customer First Name and Customer ID field to create a single field called Concatenated string. To do so, we will right click on the Customer First Name field and select Create Calculated Field…. Let us name this calculation as Concatenated string.
- There is no default function for concatenation in Tableau. However, concatenation is nothing but addition. So, we will use the + operator by typing it from our keyboard, followed by dragging the Customer ID field from the Dimensions pane and dropping it into the calculated field dialog box. Refer to the following screenshot:
- When we are done fetching the Customer ID field into the calculation box, we will see the following text, which in the preceding screenshot reads as The calculation contains errors and then is changed to The calculation is valid. Refer to the following screenshot:
- So, our calculation as of now reads [Customer First Name]+[Customer ID].
- Let us click OK and we will find our new calculated field in the Dimensions pane. Let us drag this new calculated field and drop it into the Rows shelf, just right after our existing fields, to get a view as shown in the following screenshot:
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.