0:00Hey, it's Tim here. In today's video, I'm
0:01going to be showing you how to use two
0:04string functions called Find and Find Nth.
0:07Essentially, they're two of the same
0:08functions. One allows you to find the
0:10position of a piece of text in a string,
0:12and another one allows you to find the
0:14iteration of that. So the second, the third
0:16or the fourth version of that thing. Okay,
0:19let's get stuck in.
0:20Okay, so I'm here in Tableau. I'm just
0:21going to open up the American Superstore
0:23sales. That's the second one here in my
0:25list.
0:26And it just gives us the nice data source.
0:28Now, the field we're going to be using to
0:30test this is going to be the product name.
0:32If I just drag that here onto Rose and show
0:34that to you, you'll see that it's basically
0:36just a long list of product names.
0:38If I actually just sort this out from A to
0:41Z, you'll see that it starts with some sort
0:44of numerical fields at the top.
0:46And as you go down, you see sort of a much,
0:48much bigger list of items.
0:50And essentially, the thing I want to try
0:52and do is find the position of the comma in
0:54each and every one of these names.
0:56Okay, so you can see that it's not in the
0:58same position each and every time.
1:00If I look to some of these, some of these
1:02records don't even have a comma.
1:04So if you look here, you can see that this
1:06particular record has a comma roughly in
1:08the same place.
1:09But if I go to another record, you'll see
1:10that because the names are slightly
1:12different.
1:13For example, here, you'll see that it's a
1:15slightly different length.
1:17So when we go and find this comma, we're
1:19going to first find items which don't have
1:21a comma.
1:22And then we're also going to find things
1:23with multiple commas.
1:25Okay, so let's first try and find just one
1:27comma and let's use this find function.
1:30So in order to do that, you just go over
1:31here to the calculated field.
1:33I'm just going to type in FIND to find the
1:35two functions that we're interested in.
1:39Little tip I learned today, I didn't know
1:40you could actually do this until today, is
1:43if you hold a command, you can just scroll
1:45up and down.
1:46You can actually zoom this sort of right
1:47hand side of the calculation window.
1:50I didn't know that until today.
1:52So wonderful sort of new fact I found out
1:53just accidentally during the support call
1:55with a colleague as well.
1:57So hats off to Molly and Lorna who I
1:59discovered that with.
2:01So a little great find. But anyway, find n
2:03th is also here.
2:05So first we'll do the find function or just
2:06bring in the product name.
2:08I'll just hold command whilst dragging that
2:10into the view.
2:11The view that is also control on the
2:12windows that essentially copies the field
2:15from the row shelf and to our calculation
2:17window.
2:18If I hold command again and I just scroll
2:19up, it also increases the size here so you
2:21can see what's going on.
2:23Another tip, if you highlight the thing you
2:24want to put the function around, then
2:26double click the function.
2:28Tableau will put that around the function
2:30for you.
2:31Let's hit a comma because in order to write
2:32this function, you essentially need to give
2:35it the string, which is this particular
2:36item.
2:37Then if you look over here on the right
2:39hand side, you'll see that it tells you to
2:41basically tell Tableau what you're looking
2:44for.
2:45So in this case, it's actually a comma.
2:47So the way to do that is to type in a
2:49bracket.
2:50Now you can do two single brackets or two
2:52double brackets.
2:53Whichever one you do, just make sure it's
2:55consistent.
2:56So I'm going to do two single brackets.
2:57I'm going to put a comma in there and we're
2:59going to call this the comma finder.
3:01Let's go comma finder.
3:04There you go.
3:05So if I hit apply, click OK, and then I
3:09drag this onto this particular part of the
3:12field, you'll see that some of these
3:14product names are actually quite long.
3:17If I really drag it out, you'll see that it
3:20actually does find the comma in various
3:23positions.
3:25But the number you're getting back is
3:27slightly strained.
3:29It's saying there's 448 here and you're
3:31probably wondering, well, that can't be 448
3:34characters and I'd agree with you.
3:36And that's because it's aggregating this.
3:39If you look over here on the left hand side
3:41, the actual value over here on the left is
3:43essentially being summed up.
3:45So in order to fix that, so we can just see
3:46what it is for this particular product name
3:48, I'm actually just going to go in and I can
3:51select average, min or max.
3:52They're all going to do the same thing.
3:54So if I select average, you'll actually get
3:55the correct number here.
3:57So you can see that 64 is the right number.
3:59I'm actually going to just choose a min in
4:01this particular case just to show you that
4:03it's the same thing.
4:05The reason the min does this is also only
4:06gives you one decimal point.
4:08I think it's because of the way it's doing
4:10the math.
4:11The rounding, I think, works slightly
4:12better with a min and the max and it sort
4:14of treats it like an integer.
4:16Whereas I think with the average, there's
4:18actually some maths going on which returns
4:20a flat, which is slightly different.
4:22So anyway, now that we've got this how we
4:23want, let me just go ahead and choose a min
4:25here.
4:26And you can see that some of these have a
4:28zero value and some of these have a value
4:30in there.
4:31And that is essentially the position of the
4:33comma.
4:34So now we know that position, you can then
4:36go ahead and do things like return all the
4:39characters up until that point.
4:42So let's go ahead and use something we
4:44learned yesterday on the right and left
4:46function, which is to create another field.
4:48And this is what I was talking about
4:49yesterday in a video yesterday.
4:51I said that you could essentially replace
4:53the number in the right and left function
4:55with any other calculation.
4:57So I'm going to go ahead and do exactly
4:58that.
4:59Now I'm going to say command drag in
5:01product name into our view.
5:04Let's make that larger.
5:06I'm going to highlight it and then I'm just
5:07going to hit right on here to get that
5:09function around that.
5:11I'm going to hit a comma here.
5:12And now it's asking us for the position we
5:14'd like to use.
5:17The position in this case is going to
5:18essentially be this calculation.
5:21So I can do this.
5:22I can essentially just drag that in here
5:24like that and it essentially just brings
5:26that in.
5:27And so if I just do this and I call this
5:30right using comma finder.
5:33And hit apply.
5:37You see there's an issue here.
5:39Can't mix aggregate and non aggregate
5:40functions with this.
5:42Sorry, let's try again.
5:44Can't mix aggregate and non aggregate
5:45arguments with this function.
5:47Now, if you've watched some of our previous
5:48videos, you'll also know that the attribute
5:50function will help us fix this.
5:52So let's just go ahead here and put the AT
5:54TR around this.
5:56If you don't know why, go ahead and watch
5:57my video on the attribute function.
5:59It explains this in a lot more detail.
6:01And now you'll see the calculation is valid
6:03.
6:04And now we're ready to go hit apply.
6:06And now we can go ahead and grab that field
6:08right using comma finder.
6:11Put that over there and click OK.
6:13Let's close this up so we can see what's
6:14exactly going on.
6:16We've used the right.
6:17I should have used the left.
6:18I could probably hear some of you telling
6:20me that.
6:21So let's go ahead and actually change that.
6:23So let's actually replace this with the
6:25left function in here.
6:27So let's do that properly.
6:29Hit left.
6:30Hit apply.
6:31I'm working pretty quickly here.
6:32But so you can see sort of what's going on.
6:34And you can see here that it's returning
6:36everything included in the comma.
6:38So if I wanted to adjust that, I could then
6:40go back in here again and just reduce that
6:43by the number one.
6:44I can actually do maths in here.
6:46I can just do minus one in there because
6:47this returns a number.
6:49And so if I minus one from that, that
6:50should get rid of that.
6:52And now we have a perfect sort of looking
6:54field.
6:55We've just returned text where there is a
6:57comma and we've only returned the text up
6:59until the comma, not including the comma.
7:02So that's the find function.
7:04We sort of elaborated the point a little
7:05bit.
7:06We exaggerate it, use the right function as
7:08well as the find function to sort of show
7:10you how that works.
7:12But that is a very nice, simple string
7:14calculation that anyone can do.
7:16It's really, really simple to understand.
7:18And you can start to work with it.
7:19OK. The next one we're going to do is the
7:21find nth function.
7:22So let's go ahead and actually just look at
7:23that.
7:24Let's create another calculated field.
7:26Let's go in here and type in find.
7:28And you'll see the find nth function
7:29essentially times the position of the nth
7:31occurrence of a string.
7:33So it's very much like our previous one,
7:34but we can actually tell it to go and find
7:36a particular one.
7:38So the second, the third or the fourth one.
7:41So let's go ahead and actually create that.
7:43This time round, I'm actually going to
7:45create a new sheet and I'm going to drag
7:47order ID onto rows to give us the order IDs
7:50of a particular thing.
7:52And the reason I've used this is because it
7:53has two hyphens.
7:54So I want to target the position of the
7:56second hyphen.
7:57OK, so let's go ahead and create a new
7:58calculated field.
8:00I'm going to hold command and drag in order
8:02ID.
8:03I'm going to highlight it. Then I'm just
8:04going to type in find nth.
8:06You're sort of getting a glimpse of how you
8:08start to work with Tableau now if you're
8:10sort of really comfortable.
8:11And now you'll see that I've basically got
8:13the function set up correctly.
8:15I just need to add a couple of things.
8:17One comma to specify what we're looking for
8:19and then a second comma to specify which
8:22one we're looking for.
8:24So I'm looking for the second iteration of
8:27the hyphen.
8:29OK, so let me just zoom in there so we can
8:30really sort of interrogate this here.
8:33So I'm finding the nth, which is the
8:34function we just learned.
8:36I'm using the order ID. I'm finding the hyp
8:38hen. I'm finding the second hyphen.
8:41OK, so second hyphen finder.
8:46OK, so that's apply. Let's click OK.
8:50And now if we drag that in.
8:53We should I dragged it into the rows, I
8:55shouldn't have done that, I should have
8:58actually dragged it on to where has it gone
9:00onto ABC here.
9:01So we just get the number. And again, we
9:02have that issue we had before. It's aggreg
9:04ating it.
9:05So we'll just go ahead and choose a min.
9:08And now we have the correct number because
9:09these are all IDs.
9:11Every single one of them has exactly the
9:13same length. And therefore the position is
9:15exactly the same each and every time.
9:18So there you can see the find nth function
9:19working is telling us that it's the eighth
9:21position in this string.
9:23OK, so that is a really, really simple
9:25function. The find function, you can use it
9:28pretty much anywhere you want.
9:30Now, as a bonus tip, I'm actually going to
9:32show you how to then do some really, really
9:35interesting things with this.
9:37There's one more function I'll show you,
9:39which is I'll chuck this in as a bonus one.
9:42This is called the length. So if I just
9:44type in LEN, you'll see that this will
9:46essentially return the length of a piece of
9:48string, not a piece of string, a data piece
9:52of string.
9:53I can never get my head around that. I just
9:55always, always say that. I don't know why.
9:57But anyway, this will return the length of
9:58any text, essentially. That's the way to do
10:00it.
10:01So if I just type in order ID and we
10:03highlight it and then we double click the
10:05length function so it goes around it.
10:08That's all I need to do. There's nothing
10:10more to it. If I just type in LEN, we can
10:13hit apply, hit OK, and then drag this in
10:16into this place again.
10:18And it gives us another one. So now I can
10:20see the length of the string. Don't forget,
10:22it's aggregating this again.
10:24So let's go find the min. And now we have
10:26the actual length versus the full length.
10:30OK, so we have 14 and 8.
10:31So what you can then do is you can do maths
10:33like this. If I just double click in the
10:35measure values field, I can just do this.
10:38I can say take the length and minus it from
10:41the position of the second hyphen. OK.
10:46And that will return six. And what can I do
10:48? I can say right.
10:53And I can use this other function, just
10:55holding command while I drag it in. And it
10:57's going to complain because something's
10:58broken.
10:59So let's let's let's stop being too
11:01ambitious. There's not enough space to work
11:02there. So let's try this again.
11:04So let's say right. This is a function we
11:06had before. And I'm essentially going to
11:09try and use this.
11:10And then we're going to get an error here.
11:11Right is being called with integer. Did you
11:14mean string float?
11:16Yes, I did mean string float. So what I
11:18forgot to do previously when I typed that
11:20is I forgot that I also have to basically
11:24give it a piece of text to call up.
11:27So in this case, that's the order I.D. So
11:29again, I hold command, drag that into the
11:31view.
11:32This should clear the error aggregate and
11:34non aggregate. So we know what to do here.
11:36A TTR. OK, so this is a really over
11:39engineered calculation for something really
11:41basic.
11:42You could have just done a split function
11:43on this, but now you can start to see how I
11:46've pieced together these functions that I
11:49've taught you the last few days to create
11:52something really over engineered.
11:53But essentially, let's call this a right
11:57part of the order I.D. OK, let's just do
12:00that right part of the order I.D. and then
12:03put that in next to order I.D.
12:05And we should just get the number. So just
12:07so we can go through the mechanics of that.
12:09I know we've done a lot there. Let me just
12:11close this again and I'll just go through
12:13this super, super slow.
12:15So we've got our order I.D. field. OK. And
12:17we've got our length. Now, the length is a
12:20new function.
12:21I showed you it finds the length of any
12:23text that you provide it.
12:25So it could be a field, it could be a
12:26paragraph. It will just find the length.
12:29The second hyphen finder finds the nth
12:32version of a particular thing.
12:33In this case, it's going to look for the
12:35second hyphen in the order I.D. field.
12:37What that gives us is essentially the
12:39position of the hyphen.
12:41And then what we're doing is we're taking
12:43that away from the length of the entire
12:45string to figure out how many characters
12:47from the right hand side the string
12:49actually is.
12:50And then we're taking the right function
12:51and using that value on order I.D., which
12:54is actually an attribute in this case, to
12:56find the final answer.
12:58OK, so completely over engineered example
13:01there. But you can follow along.
13:03You can use superstore sales to do this. If
13:05I've totally lost you, just just rewind and
13:08watch it again.
13:09Trust me, you'll get it. Just go through a
13:11little bit slower. You can even play me at
13:12half the speed if you want to and you'll
13:14eventually get there.
13:16But it's these sort of functions are really
13:18, really crucial because as you just saw,
13:20you can build them up to solve very
13:22interesting problems.
13:24Let's say you've got a particular piece of
13:26data where the position of something
13:27changes all the time and you need to be
13:29able to target it in the string calculation
13:32.
13:32So you can do something else like replace a
13:34value or clean up a value or clean up a
13:36data issue.
13:37This is the kind of function that's really
13:38going to help you out. The other way you
13:40could do this is regex, but that's a
13:42function for another day.
13:44OK, so that's pretty much the video in a
13:45nutshell. If you've enjoyed this video, you
13:47know what to do.
13:48If you haven't, let me know in the comments
13:49below. Let me know what you'd like to see
13:51instead.
13:52I say that every video and I know people
13:53put comments in and I haven't replied to
13:55all of them.
13:56I haven't made videos of everything I've
13:57replied, but eventually we'll get there. We
14:00've got to start somewhere.
14:01So, yeah, thank you. I'll catch you in the
14:02next one.
Tableau | Data prep | Data visualisation