Quick Tip When Using the SPLIT() Function in Tableau

Today has been a tough one for me at the data school. Our first day using Tableau Prep Builder and we learnt heaps with Carl. Here is a cool tip I picked up today when using the SPLIT() function, for when right-click->split doesn’t do you were hoping for and you want a little more control! This tip applies to both Tableau Desktop and Tableau Prep Builder.

REMINDER: SPLIT(string, delimiter, token number)

  • string = the string field you’d like to split
  • delimiter = the part of the string to split at
  • token number = the part of the string to return, after the delimiter. This can be a positive or negative number. A positive number instructs the function to work left to right, a negative number instructs the function to work right to left.

Use Case in Tableau Prep Builder

I have a column called Branch ID containing strings comprised of two parts: a branch code and a branch name. I want to split this at the hyphen that separates them, giving me the 3 character branch code and the branch name in separate columns. Lets use the SPLIT() function to do this.

1. This is what I’m aiming for. 3 columns, [Split – Store Name], [Split – Store Code] and [Branch ID].

Create a calculated field to use the split function for the store name field.

2. To create the [Split – Store Name] field. Use the formula SPLIT([Store ID], “-“, 2).
[Store ID] = Field Name (column header)
“-” = delimiter (split string at the hyphen)
(+)2 = return the second part of the string. (reading left to right)

In the calculated field. The fact that 2 is positive is important because it will return the second part of string reading from left to right

3. Success. A new column has been formed with only our store names

This time lets split using a negative number instead of a positive one. This means we will read from right to left.

4. To create the [Split – Store Code] field. Use the formula SPLIT([Store ID], “-“, -2).
[Store ID] = Field Name (column header)
“-” = delimiter (where to split)
2 = return the second part of the string (reading right to left)

To conclude. +/- signs tell the SPLIT() function which way to read when looking for the the part to split after the delimiter!

Author:
Michael Bracchi
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2024 The Information Lab