Working on Preppin’ Data week 28 challenge on Tableau Prep Builder, I had fun playing around with column split and pivots because it was so easy (I should have known better that a much harder part was about to come in the form of running totals!). This is when I came across a few ways of using the “Split Values” tool, which I thought would be useful to share.
The data set originally contained 4 columns that outlined who each employee had an “interaction with” (Coworker, Customer, Manager or No One), which I pivoted into 1 column. The output looks like this:
For ease of reading, I wanted to have “Interaction With” as the column header and person’s role only in the column. This is when splitting comes into play and there are 3 options:
- The HARD way: split all the words with space as a separator and join back “No” with “One” using a Calculated Field. Very lengthy and time consuming (which is why I am not going to show you how to do it), but it was the first thing that popped in my head.
2. The “oohh I’ve discovered a new trick!” way: did you know you could use strings as separators? I didn’t and always assumed it would only work with spaces, comas, dots and the likes. But I thought “there must be an easier way to do this!” and tentatively put “Interaction With” as the separator, “Split off: All”. And to my greatest delight, it worked!
The output looks like that and I simply removed the redundant column “Split 1”:
3. The EASY way: what I hadn’t realised is how clever Prep Builder is (and I like a challenge). Whilst the previous two options were using the “Custom Split” option, in this instance, I could have simply clicked on “Automatic Split” and Prep Builder would have worked out what I want to take out and what I want to keep in my column.
The output has all that I need (“No One” in the same column) and no blank column to remove.
Pretty clever, eh? To put on my list of “things I wish I knew before I started”…