The Find, Findnth & Len functions in Tableau

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.