1. String operations
Welcome back. In this demo, we’re going to review very handy functions in Tableau on string operations. String operations are changes that can be made to words or characters (as opposed to numbers) - called strings in a lot of programming and processing languages.
Let’s start by looking at our example data set. Here we have information about dogs and their owners. We have the dog’s first name, the owner’s last name, the city they both call home, the dog’s breed, the owner’s age, and the dog’s age. There’s only one problem: these six pieces of data are clustered in two different fields. In one case they’re separated by a vertical bar, often called a pipe, with the separation used here known as ‘pipe delineated’, and in another, by a single space. In an ideal world, each of these pieces of information has a separate field, and therefore we would have six fields instead of two.
How do we do this? We can use Tableau calculated fields and string operations to make them.
Let’s start with the owner age and puppy age fields. In this case, the owner age is always 2 digits, and the puppy age is always 1. We can make use of this in our calculated field. For owner age, we can tell Tableau to create a new field by taking the leftmost two characters, in this case, the owner’s age, from the owner age and puppy age field:
When I add it to my table I can see it working correctly - but I’m going to click on the field and quickly convert it to a whole number:
Now let’s look at names, cities, and breeds.
Let’s start by building a calculated field for dog name. This is trickier: there isn’t a set number of characters for each dog name: some names, like Max, are 3 characters long, and some, like Cooper, are 6 characters long.
Instead of using a set number of characters, we can use a very useful feature in Tableau. Right-click on the field and select “Transform” and then “Custom Split…”
You’ll then be prompted to enter the separator of the fields - in our case, a pipe, or vertical line “|” - as well as the number of columns you want to split the field into. Since we want one for dog owner, one for owner last name, one for city, and one for breed, we’ll enter 4.
Immediately we can see four new field are created.
If we drag them into the table, we can see that the data has correctly been separated.
If we right-click one of the generated fields, we can see that Tableau has generated the string operations we’d otherwise need to do manually.
We’ll quickly rename each of the calculated fields and remove the original concatenated field from our table:
Things are starting to look a lot cleaner!
Now, let’s say we wanted to make some flags for our data set; for example, an indicator Y or N for if the city the dog resides in is Tokyo. We can make a calculated field, Tokyo flag, and use the CONTAINS function, which simply parses a field for a specific string, in this case, Tokyo, and returns a TRUE value if the string is present in the field, and FALSE otherwise.
Similarly if we got notified that Charlie the Boxer doesn’t go by Charlie but Charles, we can use the REPLACE function and create a new calculated field:
To fix his name specifically:
In the following exercises, you’ll use string functions to build calculated fields for different paid search data types.
2. Let's practice!