The MID Function in Tableau.

0:00Hey, it's Tim here. In today's video, we're

0:01going to be looking at a function called

0:02mid. Now this

0:03this one actually caught me out in a

0:05certification exam. It came up in the

0:07partner consultant exam

0:08that you have to do if you're a tableau

0:10partner and it's just one of those

0:11functions where I've

0:12never had to use it so I just had no clue

0:15what it did so I guessed I didn't get the

0:17question right

0:18unfortunately but nonetheless I didn't stop

0:20the certification so what I wanted to do is

0:22immediately

0:22make a video about it because if it's

0:24something that I don't know then hopefully

0:25it's going to

0:26be useful for everyone. So I'm just going

0:28to connect to Superstore sales here and we

0:30're just

0:30going to get stuck in. The first thing I'm

0:32going to do is I'm actually going to bring

0:33in some

0:33subcategories okay so subcategories tend to

0:36be good because they're nice and small and

0:38we can

0:38kind of see the structure it's not a long

0:40list as well and I'll just to just to make

0:43this table make

0:44more sense I'll bring in sales and we'll

0:46keep it at that. I'll go to format font and

0:48I will also

0:49change the size of this text so it's easier

0:51to see so it's much much easier for

0:52everyone to see.

0:53Okay so now that we've done that we can get

0:56stuck into the mid function so let's go

0:58ahead and close

0:59a formatting tab and create a calculated

1:01field and I'll put this window to the right

1:04hand side here so

1:04all I need to do is type in mid and you can

1:07see the function comes up now if you've

1:09never used this

1:10function I want to hear from you in the

1:11comments below because I also think this is

1:13harsh of

1:14Tableau to put this in the exam because who

1:16on earth has ever used this function and

1:18actually

1:18when you go through the use case I just I

1:21've just never found a need for this

1:23normally if you're

1:24doing this kind of thing you might use

1:26something a little bit more structured like

1:28regex to kind

1:29of extract what you need but now

1:30nevertheless let's go ahead and look at the

1:33mid function so

1:34just over here on the right hand side you

1:36see you get a very lengthy description of

1:38what it does

1:39essentially it allows you to return text

1:41starting from a specified position in the

1:44string and then

1:45you've got an optional second argument

1:46which tells you how many characters it

1:48should stop after okay

1:50so let me just break that down into sort of

1:52plain English I don't know why it's called

1:54mid I think

1:55this is maybe database terminology right so

1:57we're going to call this on the subcategory

1:59field so

2:00that's all good we're going to hit a comma

2:02and then we're just going to say two okay

2:04we're just

2:04going to choose an arbitrary number just to

2:06make that work and we just call this subcat

2:09mid two

2:10okay and once we do that we hit apply and I

2:12'm just going to bring it into the field

2:14right next

2:15to subcategories subcategory so you can see

2:17what it's doing so now let's drop that in

2:20and you can

2:20see that essentially if I just sort of

2:23decipher this and it's basically taking a

2:25subcategory

2:26field okay and essentially it's going to

2:29get to the second position in this string

2:32so if I take

2:33my little subcategory string and I go here

2:36to accessories you'll see that it goes to

2:38the second

2:39position so the first one is a the second

2:41is c and then from c it returns the rest of

2:44the string

2:45that's all it's doing so in pretty much all

2:47of these all I've done by selecting two is

2:49I've

2:50emitted the first letter okay let's clear

2:52that and let's change this to four so you

2:54can see that

2:55working across all of them hit apply and

2:57now you can see that it goes to the fourth

2:59one so let's

3:00see here appliances you have a one two

3:02three and then at l you have the fourth one

3:05so that's where

3:06it starts that's what it perceives as the

3:08first letter in the word or whatever

3:10character and then

3:11it starts alliances over there now there's

3:14an optional second argument which is if I

3:16hit a

3:17comma and then I say two it's basically

3:19going to tell it once you get to that

3:21fourth position

3:22return me just two characters essentially

3:24you're specifying the length of what it

3:26should return

3:26so now if I hit apply you'll see that I

3:29just get two characters because essentially

3:31it's finding

3:31the fourth position it's treating that as

3:34number one and then it's returning two

3:36characters after

3:37that so one and two and that's what you get

3:40okay so it's kind of a weird function I

3:43just I can't

3:43think of a use case where this is this is

3:46going to be what I'm doing because if you

3:48're specifying

3:49this sort of structure then you probably

3:51have some pretty structured data so you

3:53might as well just

3:54go and do like a left or a right function

3:56right and return various things you might

3:59also as for

4:00example specify in another way it's more

4:02common that you you typically need to

4:04dynamically

4:05specify where to start and finish and so

4:07maybe that is the use case because what you

4:10could do

4:10is where it says four is you could replace

4:12this four with another function that looks

4:15for the

4:15position of a specific character okay so if

4:17you may be looking for I don't know an out

4:20sign you

4:20could maybe get this mid function to return

4:23everything after the out sign up to an

4:26unlimited

4:27number of characters right in an email

4:29address so maybe that's I've really sort of

4:31built a far

4:32fetched use case there and tablet will

4:33automatically split that email address so

4:36you don't need to use

4:37that function to do that but if you had to

4:38that's sort of like a brute force way of

4:40doing that okay

4:41so just to give you an example let's let's

4:43go ahead and actually do that example I'll

4:45create

4:46like an arbitrary field and I'll just call

4:48uh john.smith@google.com so let's say I

4:56wanted to

4:57return everything after the outside because

4:59I wanted just the domain let's go ahead and

5:02just

5:02show you how that looks like so let's just

5:04say john.smith uh enter we'll call this our

5:07field

5:07our field test and hit apply okay and if I

5:11bring that in you see it applies to every

5:14single row so

5:15now we can see that's that nice and working

5:17so now we can go ahead and use that in our

5:20field so let's

5:20go ahead and use the mid function let's

5:22make this a lot bigger and we're just going

5:24to bring in field

5:26test okay and what I'd like to do is find

5:28the position of the at sign so in order to

5:31do that

5:31if you do the find function you'll see find

5:35will return the position of a particular

5:37thing so what

5:38I'm going to do is on a new line I'm just

5:40going to type the find function okay and I

5:42'm going to bring

5:43in the thing I'm looking for which is the

5:46field test and the thing in the field test

5:50that I'm

5:51looking for is the at at sign so let's do

5:54an at where's my at sign here so find will

5:58go find the

5:58position of the uh at sign so I'm going to

6:01grab that and I'm going to put that in here

6:03inside of

6:04the mid so what this mid will do is it will

6:06use the position of the at sign to choose

6:08where to start

6:09and what I would like to do is I will put

6:12that find uh in its own uh bracket so that

6:18I can add

6:18plus one because I don't want the position

6:21of that uh find the at sign I want the

6:23position of the next

6:24character so if I add a plus one to that

6:26that all makes sense so this should leave

6:28me with the url

6:30of the email I'm fairly certain when you

6:32automatically pass things out in Tableau

6:34this

6:34is what it's doing so let's go ahead and

6:37drop that in there and boom you just get

6:39the url so

6:40um I've sort of really gone out of my way

6:43to sort of show this uh working maybe that

6:45's a way maybe

6:46that's a useful thing you might be able to

6:48use maybe you have some semi-structured

6:50data you can't

6:50be bothered with regex and the mid function

6:52is going to be a nice little use case for

6:54you

6:54but hopefully that's helped you and uh it's

6:56helped me reinforce what the mid function

6:58is at least in

6:59my own mind the next time it comes in a

7:01certification exam you don't have to worry

7:03about it okay thanks

7:04for watching if you've enjoyed this video

7:06you know what to do hit subscribe like

7:08share this video

7:09with other people who might like it and be

7:10sure to check out TableauTim.com where I

7:12have all the

7:13same videos but in a nicer more browsable

7:15format and of course check out the social

7:17media channels

7:17as well TableauTim pretty much anywhere and

7:19also the discord channel which is going to

7:22be in the

7:22link below all right get stuck in and I'll

7:24catch you in the next one