The Split function in Tableau.

0:00Hey it's Tim here. In today's video I'm

0:01going to be showing you how to use the

0:03split function

0:04in Tableau. The split function is one of

0:06the most important string functions

0:08and in some cases Tableau actually does

0:10this for you behind the scenes. So I'm

0:12going to show you

0:13where it does that and I'm going to show

0:14you how it writes the calculation so you

0:16can write your

0:17own split function to do whatever you need

0:19it to. Okay let's get stuck in. Okay the

0:21first thing I'm

0:22going to do is I'm going to connect to

0:23Superstore style. I'm going to correct to

0:24the American version

0:26which is the second one here in the list.

0:28We're just going to let that load up and

0:30then what we're

0:30going to do is we're going to use the order

0:32id to do an automatic split first. Then I'm

0:35actually

0:35going to walk you through how it works.

0:36Then we're going to create our own one. We

0:38just let this data

0:39set load. Okay now the data set is loaded

0:41we're pretty much good to go. So what I'll

0:43go ahead and

0:44do is I'll right click on order id and what

0:46you'll see is that we have this option here

0:48to transform

0:49and then split. So if we select that what

0:51Tableau will do is it will automatically

0:53analyze the field

0:54then it will see what it can use to split

0:57then it will split it by the number of

0:59things it thinks

1:00are in that particular string. So let me

1:02just show you what that means. Let me

1:03select split. You'll

1:04see that it creates three fields. We'll dig

1:06into that into more detail in a second. If

1:09we drag

1:09order id onto rows add all members and then

1:11if I hold shift and actually drag all three

1:14of these

1:15in it will bring them all in in the same

1:16order that I grabbed them. So holding shift

1:18select

1:19multiple items drag them in you'll see that

1:22this is the list that we get and if I right

1:24click on

1:25this second split here and just make this a

1:27little larger we have everything we need on

1:30screen and

1:30let me just click inside of the split

1:32function there and then it gives us an

1:33example of the

1:34formula. So now I can talk a bit more about

1:37it. Notice that when Tableau created the

1:39split it

1:39created a calculation for us. So in that

1:42instance the reason I know that is because

1:44there's a little

1:44equal sign here if you just see where my

1:47cursor is next to the hashtag anytime you

1:49see an equal sign

1:50that's essentially a calculated field that

1:52's been created. So when Tableau creates

1:54these it puts

1:54them in and amongst other calculations for

1:57you as if you'd created them. And so the

2:00really cool thing

2:00here is you can see what Tableau has done.

2:02It's essentially using the split function

2:04on order id.

2:05You're giving it the hyphen which is the

2:07delimiter that you're going to use in this

2:09particular case

2:10and then the two is essentially the

2:11instance that you're targeting. So in this

2:13case it's

2:14targeting the second hyphen and it's going

2:17to give us everything up until that second

2:19hyphen

2:20as long as it wasn't already split if that

2:21makes sense. So let's have a look at

2:24another one let's

2:25see how different they are. You'll notice

2:27that really the only thing changing is the

2:29number of

2:29characters and again it's using the exact

2:31same setup. Now if we look at the

2:33description it says

2:34returns a substring from a string sounds a

2:36little bit a bit of a tongue twister

2:38sentence that

2:39as determined by delimiter extracting the

2:42characters from the beginning or end of the

2:45string.

2:45Okay so it's essentially a very very simple

2:48function. Let's go ahead and write our own.

2:50If I go in here to the product and we look

2:54for a look for a field called product name

2:57let me just

2:58add this next to the order id and see how

3:00these are set up. You'll notice that

3:02yesterday in the

3:03find end video we used a comma so in this

3:05case I'm going to use a comma as the delim

3:08iter for our next

3:09data set. Let me open up a new tab just so

3:11it's a little bit cleaner bring the rows in

3:14add all

3:14members and now we have all the products

3:16and what I'd like to do is just drag this

3:18out and what I

3:19want to do is essentially find everything

3:22up until the comma anywhere where a comma

3:25exists. So let's

3:26go ahead and create the field right click

3:28create calculated field we're just going to

3:30highlight

3:31this hold command while scrolling up to

3:33make it larger and then we're going to on

3:36the right hand

3:37side use the split function then double

3:39click that it puts it round brackets and

3:42then now you're

3:42pretty much ready to finish writing it. I

3:44'll put in the full structure of everything

3:46like I'm doing

3:47now and now all we need to do is pretty

3:49much define what's going on so I'm going to

3:51look for

3:51the first comma so in order to do that I'll

3:54just put two speech marks then inside of

3:57that I'll put

3:57a comma and that's pretty much the function

4:00in a nutshell. Now you'll notice in the

4:02other

4:03calculation it actually turned it into an

4:05integer for us it didn't have to it could

4:07have left it as

4:07measures and then it would have been a sort

4:10of a number that might have had fractions

4:12but in this

4:13case it didn't really have anything so

4:15putting the integer function just makes

4:17that calculation a

4:18little bit more efficient when it's being

4:20used elsewhere that's why that was there.

4:22So let's go

4:22ahead and call this split on comma and hit

4:26enter and then hit apply click ok and now

4:30we can drag

4:31this onto the view and you'll see that in

4:34most cases it's pretty much done the job so

4:37you can

4:38see here 3D systems Q printer it's got that

4:41up until the first comma which is perfect

4:44but notice

4:44in any case where we don't have a comma it

4:47's just brought everything back in so the

4:49default for

4:50behavior for this is not to null out when

4:52it doesn't find what it's looking for it's

4:54just to

4:54return everything that's there so you can

4:56see here the three ring staple pack that

4:59had no comma so

5:00it's just returned everything that was

5:01already there however this one above it one

5:03quarter

5:04fold party design innovations and white

5:06envelopes has a comma there and now we're

5:08not getting that

5:09second item what you could then do is you

5:12could then duplicate this and you could

5:15return everything

5:16after an assumed second comma so basically

5:19what this is doing is returning everything

5:23that's left

5:23essentially in this particular case in the

5:26instances where we have a comma so this is

5:28a

5:29copy i'll leave it called that so you can

5:31just see the difference let's drag this out

5:33and now you'll

5:34see that you don't have as many fields

5:36because for those ones everything that was

5:38already left in the

5:39field after the first delimiter is still

5:42there and we have nothing else to pass out

5:44whereas for these

5:45other items there is actually more sex

5:47there so having a second split will

5:49actually give us the

5:51remainder of the text so if you've ever had

5:53a hierarchy that's split out by commas in

5:55one field

5:56sometimes this is done on databases to save

5:58columns and space and this is essentially a

6:00trick you can

6:01use in an analytics store to sort of split

6:03that up in other cases sometimes a number

6:05of spacings

6:06can donate a hierarchy in a data structure

6:08i've seen that as well so splitting things

6:10out can also

6:11help you break that hierarchy down a little

6:14bit more and help you sort of articulate

6:16whatever

6:16you're trying to articulate with the

6:18visualization a little bit more clearly

6:19because you can now

6:20navigate the structure that's there by comm

6:22as you'll actually notice this set here

6:24actually has

6:25two commas so in this particular case i

6:28could just duplicate this one more time and

6:31hit edit and now

6:33we can hit three on this one hit apply and

6:36then bring that in and now we actually have

6:40absolutely

6:41everything so you can see that this keeps

6:43them going now we can keep going until we

6:46find everything

6:47and essentially that's you know something

6:49that's very very easy to do but another

6:51thing we could

6:52do is we can count the number of commas in

6:54a piece of text as well and so i'll show

6:56you how to do that

6:57in another video but for now this is pretty

6:59much it for the split function we're

7:00keeping it super

7:01simple here just wanted to show that to you

7:04um so you could use it now before we go the

7:07other thing

7:07i want to do is just show you the custom

7:09split capability the custom split

7:12capability essentially

7:13allows you to dictate how the split is done

7:16and so this is the automatic way of doing

7:18the same thing

7:19let's say that i use a delimiter for the

7:21comma here and click ok now you'll notice

7:24that nothing

7:25happens because this order id it calls it

7:27number four basically didn't have a comma

7:30so that was

7:31actually not the right thing to do the

7:33better thing to do was to go to the product

7:36name and

7:36here we can create a custom split and then

7:39you can see here that i'm actually typing

7:41on my keyboard

7:42before we've done anything and the thing i

7:44want to target is a comma and you can

7:46actually tell it

7:47which columns to bring so you can tell it

7:49the first four columns the five four column

7:51whatever

7:52you want and it will make sure that it

7:54brings that information all across so if i

7:56select all then it

7:57basically looks for all the commas and it

7:59essentially splits it out for you now

8:01because i haven't called

8:02this product name split one and two and

8:03three it will go ahead and use those names

8:05when i hit ok

8:07and so we can see here that in some cases

8:09there are actually four levels of the

8:12hierarchy and

8:13that's essentially what we've got here

8:15product name split split on comma and then

8:17split on comma

8:18number three if we clear the sheet just by

8:20clicking this x here at the top and then

8:22bring

8:23these four out as they're selected you'll

8:25see the hierarchy in full pretty much

8:27repeated there and

8:28now you can start to see how this comes

8:30together and so you can see that it's built

8:32out of hierarchy

8:33it's kind of cool in this particular case

8:35you have aco press board covers there's two

8:38sort of

8:38sizes and there's three different levels so

8:41immediately you can see the power of using

8:44this to break down a hierarchy was

8:46previously just one bit of text just again

8:48to remind you

8:49that let me bring that in here and you can

8:52see that these were actually all in the

8:54same

8:55sort of level of hierarchy if i actually

8:57expand this out maybe you can see it a

8:59little bit more

8:59clearly there we go you can see that a

9:01little bit more clearly it was just all a

9:03bunch of items but

9:04if i remove the product name and i use our

9:05new split function you can see that we

9:07actually have

9:08a hierarchy to follow and so that's pretty

9:10much it for the video and if you've enjoyed

9:13it you know

9:13what to do if you haven't drop me a comment

9:15below and we'll catch you tomorrow when we

9:18'll take on

9:19the next function take it easy