How to do multi-line calculations and edits for Tableau: Quick Tip

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

0:02going to show you something really cool. I

0:03'm going to show you how to do multi line

0:05calculations efficiently for tablet. Okay.

0:08So in order to show you this, I've created

0:11like a fake example using some mock data

0:15from mokaru. So I'll go ahead and connect

0:16to the Excel file that I've got here. Now

0:18it's this one, which is not labeled to be

0:19open during the demo. So let's open that

0:21file. And you'll see that I have two sheets

0:23. The first sheet is the raw data set that I

0:25'm actually going to be working with. Don't

0:27worry, these aren't real details. It's

0:29generated from my

0:29career. And essentially, the task I have in

0:31hand is let's say I've been working on a

0:33workbook. And then three months later, HR

0:36send me a data set and say, Hey, can you

0:38update the surnames to these new values.

0:40But instead of sending me the whole entire

0:42data set again, they only show me the 27

0:46rows that need updating surname and old

0:48surname, essentially. Now you'd probably

0:50have an ID field in here. So you you're

0:52doing like a proper match and the right

0:54things. But essentially, I'm just going to

0:56use this as a simple example of what we

0:58would do if we were to do a mock data set.

0:58So this is just a simple example of what we

0:59want to try and do. Now the other

1:01limitation here is that you're probably

1:02working with a published data source.

1:04Because if you aren't, then the actually

1:06most efficient thing to do is to just join

1:08these onto existing data, create a

1:10relationship. And boom, you've already got

1:11your old surnames that have related to your

1:13new surnames. And you're sort of ready to

1:15go. Okay, you don't need to sort of worry

1:17about what's going on. So what are we going

1:19to do here? Well, let me show you how this

1:21is going to work. So I'm going to go into

1:22sheet one. And many of you, many of you

1:24have probably done this, you have.

1:27The last name column over here. And you'll

1:29see that you have everything there. And the

1:32only real way you can do this is maybe to

1:34right click one of these, edit the alias

1:36and change them manually go find each of

1:38them. And you know, that's 27 bits of work

1:41that you're going to have to go and do. And

1:43some of you might, you know, just optimize

1:44yourselves and do that in five minutes. I'm

1:46going to show you an even quicker way. Now,

1:49that's not to create a calculation and

1:50manually write the calculation, I'm going

1:53to show you how to do this efficiently

1:54using another tool. Now, many of you might

1:56want to do this.

1:56So let's go into the next tool. Now, many

1:58text editors actually have the ability to

2:00edit on multiple lines. If you've worked in

2:02web development, you'll already know about

2:04this. But in essence, I'm going to use

2:06something called VS code. Now VS code is

2:09free. It's a very good tool by Microsoft,

2:13essentially for code editing, and it

2:14supports a whole load of programs. So this

2:15is completely tool agnostic. And I'm not

2:21showing anything new here. This has been

2:21done in web development and many other

2:22programming languages before.

2:23So I'm just going to create a new file. And

2:24I'm going to select a language. Now, you

2:26don't need to do this, this is work. This

2:28will work just as a text file. But the

2:30reason I'm selecting a language is because

2:32I want some familiar formatting. So if I go

2:35down here to the bottom right, and then

2:38select plain text, and then I can type in

2:41SQL, you'll see that it comes up with SQL.

2:43And then I can use a SQL formatting for

2:44this. This means when I write for example,

2:46case, it recognizes that's a statement and

2:49I can then start to use that, so on and so

2:51forth.

2:52So it behaves like it's writing SQL, but we

2:54're not we're just using it to help us

2:56format and help us understand what's going

2:58on. Okay, so let's actually focus on the

3:00task in hand here. So one of the things I

3:03could do is write a case statement. So let

3:05's, let's sort of start writing that. So I

3:07could say case. And then I could say, last

3:10name, this is probably the way most people

3:13would go. And then they'll say when, you

3:16know, x, then y, okay, and then they might

3:20repeat that over multiple lines.

3:21And they'll just sit there and they'll type

3:23sort of 27 lines of that. A common thing I

3:26do is actually paste. Now there's a little

3:28bug here where sometimes if you copy and

3:30paste too quickly, it actually paste it

3:32into the connection. A friend of mine on

3:35social media was actually talking about

3:37this and it's, this bug has actually hit me

3:39. So kind of annoying. But nonetheless, let

3:42's go. So as I paste there, you'll see that

3:45everything is happening normally. Now you

3:47might do that over multiple lines and then

3:49move forward.

3:50Now, I'm going to show you a much, much

3:52faster way. Okay, so I'm actually just

3:54going to go to VS code. And I'm going to

3:56basically just start writing the case

3:58statement. Okay, so I'm just going to say

4:00case, I'll go back to the calculation here,

4:03and I'll grab the last name. And we'll just

4:05go back here and I'll paste it. Okay, so

4:08case the last name. Now, the thing I need

4:10to do next is write when over multiple

4:13lines, but you see, I don't want to sort of

4:14start counting here. I know there's a line

4:14number on the left hand side. Ideally, I

4:14want to do it over multiple lines.

4:14But you see, I don't want to sort of start

4:15counting here. I know there's a line number

4:17on the left hand side. Ideally, I want to

4:19know exactly how many lines I want to bring

4:21in. So in order to sort of, you know, skip

4:24this step, what I'm actually going to do is

4:26go to my source data source. Now, this is

4:28the version that I can open during the demo

4:29, but it's an exact copy of my data set. So

4:32I'm going to go here into the updates

4:33column, and you'll see that the old surname

4:36is essentially what our current last name

4:38is. So I can grab this old surname, I'm

4:41going to copy this. And when I copy

4:43everything in Excel,

4:43and I go back to VS code VS code

4:44understands that everything was on a new

4:46line, because essentially, I'm copying from

4:49one structured place to another. So when I

4:51hit paste, it actually puts them all in a

4:54new line. So I don't actually have to count

4:55the number of lines, it's exactly 27 to

4:58match the 27 rows. Now we're on line 28.

5:01Because of course, the first line was the

5:01opening of our case statement. Then I'm

5:04actually going to close that and case

5:06statement there. So with an end notation.

5:08So what next? Well, there's actually some

5:11really cool stuff here.

5:12There's actually some really cool shortcuts

5:14in VS code that allow you to do multi line

5:15select, I'll put them up on the screen. But

5:17I'm on a Mac. So I'm going to hit option

5:19command and then up from the bottom hand,

5:21what that will do is it will stretch the

5:22cursor upwards across the lines that I'm

5:26going. So I can just keep going until I get

5:28to the second line. And now you can see the

5:29cursors on every line. And the key thing

5:33here is that I'm looking for somewhere

5:35structured that I can insert my text. So I

5:37've got the caps lock on, I'm going to type

5:39in when okay, and then do the first speech

5:41marks.

5:41And so you're thinking, great, I've done

5:42the first half of this, how do I get to the

5:44end of the line? Well, if I start traveling

5:46, right, you'll see that not everything is

5:48the same length. So I just can't sort of go

5:50the same number of spaces. So on my

5:52keyboard, I'm going to hit the end shortcut

5:55. Now the end shortcut is typical on most

5:57proper keyboards. If you don't have the end

6:00shortcut, just look for the end sort of

6:02notation on your operating system. There's

6:04usually a shortcut for that. But for me, I

6:05've got a like little key that I can hit and

6:07then it goes to the end of the line. That's

6:09essentially what you're trying to do.

6:10And once you've done that, you can hold

6:12shift and do the last speech marks and

6:14close that off. And then you can sort of

6:16finish typing the rest of the statement.

6:18Okay, and do that. Now you're probably

6:21wondering, well, we need to put the new

6:23surnames inside of these speech marks. So

6:26let's go back to Excel. And of course,

6:28because VS code understands the structure

6:31of Excel, if I copy these, and then I go

6:33back to VS code and make sure that I don't

6:36deselect, you can see that I'm just

6:37hovering my mouse here, and I'm just going

6:39to do the same thing.

6:39So I'm just hovering my mouse here. And

6:41then I immediately hit paste, it's going to

6:43put those names in place of where those

6:45cursor was. So there you go, I've written

6:48my case statement across multiple lines, I

6:50can then copy this, go back to Tableau and

6:53replace this. And boom, we should have a

6:56valid calculations, you can see that that

6:58is there at the very bottom. And we've

7:00written our case calculation very, very

7:02quickly.

7:03Let's say your boss turns up and says, hey,

7:05we don't do case statements, we do if and

7:07logic calculations here. So right, fine,

7:10let's go ahead and do that. So let's change

7:12what we've already written to a logic

7:14statement rather than a case statement. So

7:16how do we do this? Well, a logic statement

7:19usually starts with if so let's get the

7:21first line set up. So if last name equals,

7:25and let's just get the all code here.

7:27Okay, equals that, then that, okay, so then

7:30we've got our structure, we've done our

7:32first line. Now we know what we need to

7:35repeat across several lines. So essentially

7:38, we need to insert this last name equals

7:41before each and every when essentially,

7:44okay. And the other thing is, we need to

7:47make sure that we open each line with an

7:48else if statement, so all these wins need

7:51to be replaced with an else if so let's

7:54start with that.

7:55Now, if I highlight when you'll notice that

7:57in VS code actually selects multiple lines

7:59in like a soft highlighting. So what that

8:01means is if I then go and hit Command Shift

8:04L, it selects all of them, essentially, it

8:06's looking for some structure in my text.

8:09And if I tell it to it will go ahead and

8:11pick everything that has the same structure

8:13on different lines, then I can just go

8:15ahead and delete that and type in else if

8:18okay.

8:19Now, I've got that the next thing I

8:20probably want to do is paste the last name.

8:23Now, if I deselect in here, I'm going to

8:25lose my selection. So what I'll do is I'll

8:27sort of hack here and just go select that

8:29from last name, go back to VS code. And

8:33then what I can do is hit Ctrl V and it

8:35will paste that on every line and then I

8:37can do equals. And there we have our logic

8:39statement. Okay, so if last name equals,

8:42okay, then Scannerland else if last name

8:46equals Karivik, then to region.

8:48And it will just keep going, keep going

8:49until we get to the end. And then that's

8:52pretty much our logic statement done, I'm

8:54going to go ahead and copy that. And there

8:56you go in the time is taken to essentially

8:58write two logic statements, I've actually

9:01created a 27 line if and a 27 line case

9:05statement.

9:05And again, they're all valid. So let's just

9:07call this new surname. Okay, and to make

9:10this sort of work nicely, I'm just going to

9:13say else last name, just to make sure that

9:15the last names do sort of persist. In fact,

9:18what I'll do is I'll just give it something

9:21different.

9:21So it's easy for us to actually see what's

9:23changed. So I'll leave everything that hasn

9:26't changed as old and I'll leave all the

9:29only the new ones as changed. So hit apply

9:31and I'll click OK, go to new surname, put

9:34that next slide. And you can see there that

9:36all code changed to Scannerland.

9:36And that's our calculation done, we can see

9:39that if I actually exclude all the old ones

9:42, you remain with our 27 changed surname. So

9:45that's a pretty simple trick. And took a

9:48bit of a while to explain. But the key

9:51thing is to make sure you understand the

9:51shortcuts in your code editor.

9:52Now, pretty much all code editors will have

9:54multi line select that's sort of like a

9:56standard feature in most web development

9:58and programming tools. So just look for

10:00whatever that is, whether it's notepad,

10:03plus plus sublime, I'm using VS code, there

10:05's a whole range of other tools out there

10:07that will do something similar.

10:08And if they don't get one that does,

10:09because it will save you a ton of time. The

10:11other thing is you can use this approach to

10:14do really long logic statements that just

10:16that's the only way you can do it, you're

10:18working in a published data source, that's

10:20going to be the only way you're going to

10:22change something.

10:23This is going to save you a ton of time. So

10:25hopefully you found this useful. If you

10:26think it's found it useful, let me know in

10:28the comments. If you think someone else

10:30will find it useful, share it with them. I

10:32'd love to know what other tips you'd love

10:34to see.

10:35I'm going to just try and do this every

10:36time I come across something in my day to

10:38day work, I'm going to try and do a video

10:39at the end of the day and just show you

10:40what it's like. And yeah, maybe maybe we'll

10:43come up with a whole playlist of this kind

10:44of content over the next few months.

10:47Thanks for watching, and I'll catch you in

10:51the next video.