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.
Create a calculated field to use the split function for the store name field.
[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
This time lets split using a negative number instead of a positive one. This means we will read from right to left.
[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!