Mastering String Field Splitting in Tableau

Enhance Data Analysis with String Field Splitting Techniques

Optimize Data Visualization: Splitting String Fields in Tableau

Introduced in Tableau 9, Split Function served as an excellent addition to the existing automatic functions that are available in other lower versions of Tableau in. If we had to break it down – the Split Function has two features – both are used with a specific Delimiter or Separator (which are string special characters).

Different Splits in Tableau

The first split is known as an ‘auto split’ – in essence it identifies a common delimiter and further creates only two new columns.  

The second split is known as a ‘custom split’. Here – the Tableau developer specifies the delimiter along with the number of columns required. If you’ve found the last few lines be a bit confusing, don’t worry about it – we’re going to dive into detail and explain exactly what were talking about along with example that make it easier to assimilate all the information within this blog. Mastering Tableau often requires drawing out examples with specific use cases – it helps us to see the bigger picture and our approach in this blog will be no different.  

Use Case: Understanding what’s what and who’s who

Let us start by assuming that there is a staffing company that tracks billable working hours. The company can be a construction company, an IT consulting firm, HR firm or even a Financial department – what’s common amongst all these companies is their interest when it comes to collecting updates in regard to the active hours and money for services offered.  

The requirement is such that if a person misses the check-in and / or check-out stamp, their name and shortid (as an email) is to be displayed in the Tableau report. Along with this, an email is automatically triggered to their managers / supervisors detailing out the missing values. 

In the above use case, we are interested in only creating a shortid in email-id format for the employee names in the data. In essence, this is the part we are trying to solve in this blog.  

We shall now look at two different ways to do this in Tableau. 

Understanding the Importance of String Field Splitting in Tableau

Exploring Two Efficient Methods for Splitting String Fields

Method 1: Using Tableau Calculated Fields for String Field Splitting

Method 2: Utilizing Tableau Prep for Streamlined String Field Splitting

We’re going to solve the above use case using a sample data set. Note: Strictly for leaning purposes and easy remembrance, we have used using a list of 10 U.S President names and will create a simple email id for each name. 

Assuming we have the Column A, we need to create Column B in Tableau. The idea is to split the name into multiple pieces, isolate the first name, last name initial and concatenate these pieces with the email domain. However, the challenge faced here is elimination of middle names within the process. 

This is applicable to many cases. An example would be dates that are formatted as string 05/25/2016 (MM/DD/YYYY format). Here, we can cut into pieces using “/” delimiter.

Another example is where it can be used to identify the actual folder if the data is stored with an entire directory of information such as C:>Files>Tableau>Workbooks>LocalFile>SplitExample where “>’ is the delimiter. It can be used to identify the transaction card if the column has value VISA, MASTERCARD, AMEX, DISCOVER where “,” will be the delimiter.

It should be noted that the Split Function or creation of these new string fields can fail or affect in many ways in Tableau. We’ve listed a few below:

a) Performance can get affected as string calculations occur at row level.

b) This calculation can fail if two people have same first name and same last name initial.  
E.g. Adam Hall and Adam Hughes.  
Eventually this will cause duplicates since both names will have the same id adamh@test.com.

c) This calculation can also cause issues if either the ‘first name’ or ‘last name’ is a ‘null’.

d) The calculation can cause issues if there are hyphenated words.  
E.g. John-Paul or Emma-Claire.

e) The calculation can also fail if by mistake there are two separators right next to each other.  
E.g. 08 Hours: 45 Minutes: 25 Seconds. Here a colon “:” and a space “ ” are placed together.  
Though Tableau allows us to use multiple delimiters together, it can be a little tricky.

Leveraging Tableau’s Flexibility: Splitting String Fields for Enhanced Data Analysis

Mastering Data Manipulation: Techniques to Split String Fields Based on Delimiters in Tableau

Enhancing Data Insights: Exploring Advanced Methods for String Field Processing in Tableau

Boosting Data Visualization: Optimizing Tableau’s Functionality with String Field Splitting Techniques

Different Methods

It is finally time to bring your data into Tableau. Once we’ve made sure we have everything ready, we can proceed towards these different methods.

Method 1 – Split String Fields Based on Delimiters in Tableau

In this method, we shall use the ready ‘Split Function’ available from Tableau 9 and higher. Behind the scenes there is an actual calculated field. But if something is a ready feature, we do not have to create it from scratch which will be discussed in the next method.

1) This step can be done in the preview pane when the data is brought in or in the data pane.

a) We need to right click and select the type of split from the transform option.

b) Auto Split – The first option is an Automatic Split. This will identify the highest occurrence of the separator. In this case, it is the space. So, it will only cut into two new columns because conceptually, it is only cutting the string based on the first instance of the delimiter (first time the special character was identified) in the field.

c) Custom Split – Here, we need to specify the delimiter, the location and number of output columns – we need only one column for this step.

Note: Both Split Functions yield the same results.

d) We can see here the Split 1 field is what we need which is the first name. However, the Split 2 does not have the required last names as the middle names and middle name initial are also shown. So, the Split 2 field can be deleted.

e) The Split 1 field can be renamed as First Name. 

2) Now, we need the Last Name. So, the Auto Split will not work. To counter this we need to proceed with a Custom split at the last instance of delimiter (space).

a) We need only one column. The Split 3 field will be renamed as Last Name. 

b) As mentioned before, there are actual calculations running behind the Split features.

c) Behind the scenes, there is a reading frame either left to right or right to left – based on the position of the delimiter, the sting field will be cut into pieces. The following image illustrates it in a better way.

d) An alternate way of doing this is to use the Custom Split function to split at all instances of the delimiter. Then we can write a simple calculation that checks if the Last Name is not available, it can be moved from the middle name column.

3) In the last step, we need to concatenate the required fields and characters to get the final column. For this we will use the + concatenation operator, the LEFT() function (The left function reads the number of characters specified and cuts the string at that location) to get the first character from Last Name and the email domain characters within quotes.

a) We are also using the LOWER() function to change the entire string into lowercase. 

Method 2 – Split String Fields Based on Delimiters in Tableau

This method will explain step-by-step all the features in a more conceptual way. This is a more laborious approach. But whatever we use here can be used in many different situations. This was the only method to solve prior to Tableau 9. It consists a series of calculations, so we need to keep track of them in order to achieve the final output.

1) This is the most important step and will be repeated later again.

a) Here we are finding the position of the delimiter within a string field using the FIND() function.

b) It reads from the left-hand side character by character and identify the position of the special character specified.

c) Since it is a number, it will be a measure, it will be under measure and must be treated as a Dimension

2) We not need to get the First Name using the location with the LEFT() function

a) The left function reads the number of characters specified and cuts the string at that location.

b) We must remember that we need all the characters before the first space, so we need to subtract by 1. 

3)This step is a tricky but with the right guidance – can be easily achieved.  

a) Now that we have the first name, we need to get the last name, but some names also have middle names. So, we will need to gather everything after the first name and repeat the above steps.

b) To achieve this, we need to use the MID() function which is similar to the LEFT() function, but reads inside the string field at the specified special character.

c) We must also remember that we need all the characters after the first space, so we need to add 1.

4) We will now need to repeat the FIND() and MID() steps for this new field.

a) If there is a space, then we need all characters after the space, else we can retain the values from this field as is.

c) We can also find the number of characters and use the RIGHT() function to read from the right side which uses more steps.

 5) Now for the last step, we need to concatenate the required fields and characters to get the final column.

a) For this we will use the + concatenation operator, the LEFT() function to get the first character from Last Name and the email domain characters within quotes.

c) We are also using the LOWER() function to change the entire string into lowercase.

The advantage of creating this email id field is that it can be used as a mailto: feature in the URL action.