The Right () and Left () Functions in Tableau

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

0:01going to be doing a video on the right and

0:04left functions

0:05inside of Tableau. Okay let's get stuck in.

0:07So we're here in Tableau, I'm just going to

0:09open up

0:09Superstore sales, I'm going to open up the

0:11American version which is the second

0:12version, and here we

0:14go. Now the field I'm going to be using in

0:15order to show you this feature is actually

0:17the order

0:18id field. If we just right click on it and

0:20then go down to describe, you actually get

0:22a description

0:23of this field which is essentially the

0:25metadata about this particular column

0:28inside of the

0:28database, in this case that's excel. If you

0:31scroll down you'll see a list of basically

0:34the first 20

0:35or so items, this is really really cool. If

0:37you don't see this you sometimes get this

0:39option to

0:39load the data, so that's a nice little tip

0:41there if you want to see what's in a in a

0:43particular

0:43column just hit the load button and it will

0:45load it or it will show you the first 20

0:47rows. Now I'm

0:49going to be using the right and left

0:51functions to essentially isolate certain

0:53bits of this text,

0:55that's essentially what this string

0:56function does, it lets you choose a certain

0:58number of characters

0:59from the right or the left and basically

1:01isolate them in a calculation. So let me

1:04show you how that

1:04works, I'm going to hit close, I'm just

1:06going to bring order id onto rows here so

1:08we can see it

1:09nice and clearly. Tableau gives me

1:11notifications to tell me there's a lot of

1:13rows here,

1:13I just clicked right through it because I'm

1:15fine, I've got enough resources on my

1:17computer to be

1:18looking at that. If you want to know how

1:19many rows exactly it's over down here on

1:21the left hand side,

1:225009 marks, that's essentially how many

1:25rows we've got. So I'm going to open up the

1:27calculation

1:28window, create calculated field and we're

1:30going to call this the right function just

1:32to start with.

1:33Let's make this bigger by hitting command

1:35plus just so that it's big enough for you

1:38to see and

1:38as ever you can go over here to the right

1:41hand side, go to the string selection and

1:43just look

1:44for the function that says right, it's not

1:45right here in the list so I'm just going to

1:47type in

1:47right and it will search it for me and

1:49there we go. On the right hand side it says

1:52returns a

1:52specified number of characters from the end

1:54of the given string, that's pretty cool so

1:56let's double

1:57click on that and that will bring it into

1:59the field. Now the field we want to do this

2:01is on

2:01order id so I'm just going to go ahead and

2:03type that in there so that's our first bit

2:06of the

2:06calculation, we need to have a piece of

2:09string, a piece of string, we need to have

2:11a string inside

2:12of this data section okay. Then we have a

2:14comma after our string to basically tell

2:17Tableau that

2:17we're now moving on to declare the number

2:19of characters we want from the right hand

2:21side

2:22and if I just move this to the right I know

2:24that this data is very consistent, it's

2:26lovely and

2:26clean, Superstore always is. If we count

2:29this one, two, three, four, five, six, the

2:32last six characters

2:34are basically what we're interested in so

2:36let me hit six and that's pretty much our

2:38function. Now

2:40it's going to go on a new line here, this

2:41is a mistake sometimes people make,

2:43I don't know what I'm doing here but if I

2:45just hit double forward slash here,

2:46sometimes people will do this although I

2:49write the function like so and this should

2:51be in a capitals

2:52okay so this is pretty much exactly the

2:54same as we just did and then what they'll

2:55do is they'll look

2:56at this documentation and they'll use the

2:58double quote mark so they'll do this and

3:00then they'll say

3:02let's just pretend that I actually

3:05automatically entered this order id okay so

3:09this is what they'll

3:11do and then they'll write the function like

3:13this and they'll write it like this because

3:15that's what

3:15the documentation here says. What's not

3:17clear is that Tableau is only putting those

3:19quotation marks

3:21in there because that is a way of adding a

3:23string to this calculation. Let me just

3:26show you if I take

3:27this and put it up here what will actually

3:30happen is it will treat this as a piece of

3:34text okay so

3:35what I'll do is I'll hit enter on this put

3:38it on a new line and then comment it out

3:41and then what

3:41we'll do is we'll get rid of these and then

3:44we'll switch it over to show you that it

3:46works properly

3:47if you don't include them. I'm only showing

3:49this because I see it all the time people

3:51follow the

3:51instructions to the letter let's hit apply

3:54drag right onto order id I did that without

3:56you know doing anything and you can see it

3:59literally just took the right six most

4:02items

4:02so if we count backwards we have one here

4:05with the letter d two in the letter i a

4:07space I think

4:08I've got two spaces here no one space and

4:11then that's what how many now so let me

4:14just start

4:15again one two three four five six okay so

4:19it returns everything after the letter d

4:23pretty

4:23much okay and I think because of the way

4:26computers work um six actually means it

4:28starts counting from

4:29zero and essentially I think what's

4:31actually going on here is that there is an

4:34additional

4:34character here that I'm not oh of course I

4:37forgot this little um bracket so one two

4:40three four five

4:42six and that's why we start with the e in

4:45there I do sometimes get confused because

4:47when you count

4:48in computing terms sometimes six means

4:51seven because you start counting from zero

4:54one two

4:54three but that's programming language that

4:56's not tabular so there you go you can see

4:58it's

4:58taking the right more six and it's not

5:00working as we expected because the order id

5:03field is actually

5:04a field so in order to do that we just need

5:06to just remove this because that's actually

5:09the

5:10string in itself we don't need to put

5:12speech marks around it to make it a string

5:14it already is a

5:15string we know that by just going to the

5:17data type here you can see it says abc that

5:19means it's a

5:19string so now let's hit apply and now you

5:21'll see that that's doing the right thing so

5:23that's pretty

5:24much it for these functions right goes from

5:26the right hand side left goes from the left

5:29hand side

5:29let's just switch things up and let me just

5:32show you that very very quickly so if I

5:34just search

5:34for this field we have right here I'm gonna

5:37right click and duplicate it so I get

5:38another copy

5:40then I'm gonna rename it and call it left

5:42it's gonna disappear because I've got this

5:45search

5:46going on right now and so only looking for

5:47things that start with right so then let's

5:50go here right

5:50click on it and edit and it loads up the

5:53left and we can just change this now so we

5:57'll just hit this

5:58one here and I'll just type in left okay

6:01just to make that simple it auto completes

6:04now you can see

6:05it's exactly the same function hit apply

6:07and we're going to drag it in and you'll

6:10see that it takes

6:11the left six items so in this case ca201 is

6:15basically what it's going to pick up I can

6:19actually change it to seven because that

6:22would give me the first part of the order

6:24id and it

6:24keeps it in a nice consistent format so

6:26this is great because this works when you

6:29know the field

6:30or the information is going to be

6:31consistent every single time however most

6:34data isn't actually

6:35ever that clean so this has a very limited

6:37use case you can do something which is to

6:40dynamically

6:40find the position of something and then

6:43dynamically create this number over here

6:45and then use that to

6:47do the left function so if you think of it

6:49this way whatever you put in here can be

6:52anything it

6:53can be a calculation it can be a parameter

6:55in fact if I just go ahead and do that let

6:58's just create a

6:59parameter in this white space we'll call it

7:02number of characters okay and now that's

7:05ready to go we're

7:07going to keep it as a float click okay

7:09right click on it show the parameter it's

7:11going to come up here

7:12on the right hand side okay now I'm going

7:14to go back in my left calculation and I'm

7:16going to

7:17replace this let's just make this larger so

7:19you can see I'm going to replace this with

7:22my parameter

7:22and the parameter is called number of

7:25characters you see it comes up here as a

7:27numerical

7:29field but it actually says source

7:30parameters so let's click on that and

7:32parameters end up being

7:34purple inside of a calculation window so

7:36now hit apply you'll see that it just has

7:38one character

7:39but I can now close this and now in here on

7:41the right hand side I can just simply type

7:43whatever

7:43I want let's say I hit 8 hit enter boom it

7:46goes and finds the eight characters hit 7

7:49boom so

7:50parameters are basically user variable

7:52fields and you can enter whatever you want

7:54into them they're

7:54not stored with the data source they're

7:56stored in a separate table that sits inside

7:58of the workbook

7:59and it's loaded dynamically on onto the

8:01data set when you need it but it allows you

8:03to do really

8:03cool things like this give users a way of

8:05changing the context of the question being

8:08asked and you

8:08can build them into your calculation to

8:10make things like this really really dynamic

8:13okay so

8:13that's pretty much it for the video hope

8:15you enjoyed it in the next video we're

8:16going to be

8:17actually looking at the find function

8:19specifically find and the find nth function

8:22and we're going to

8:23be combining it a bit with these functions

8:24as well so be sure to look out for that

8:26video

8:26and when I upload it very soon all right

8:28take care if you've enjoyed the video you

8:30know what to do if

8:31you haven't let me know in the comments

8:33below a lot of you have been telling me

8:35what kind of videos

8:35you want to see on Tableau server I

8:37actually have a new way of making sure that

8:39people can submit

8:40those and we can get a bit of a community

8:42involvement in terms of choosing which

8:44videos

8:45I actually record next these functions are

8:47going to be a staple this year but we're

8:48going to try

8:49and do new videos on a Friday that are

8:51basically community voted so look out for

8:54the link to that

8:55in the description I'm going to sort of

8:57create a bit of a challenge to find the

8:58link to do that

8:59because a lot of people spam the comments

9:01so I'm going to make it a little bit hard

9:02for people to

9:03find but if you look in the description you

9:05'll know what to do and yeah suggest some

9:07videos

9:07you'd like to see I'll catch you in the

9:09next video