Original subtitles

In this lesson, I'd like to continue to look at some options that creating relationships allows us

to do so.

We did see in the previous lesson that we were able to use now the fields from the two different tables

and we could use those in our visualization.

So we were using our job grade and our cost, for example, and obviously it worked.

I just want to show you, though, quickly that if you do not have a relationship, what actually happens?

So let's go back to our model.

Let's just delete that relationship.

So there's no relationship between the two.

Now, if you go back to your visualizations, you'll see that you get this where it just basically gives

you the total cost for each of your department when this visualization is also doing the same.

If you get that as a result, you then know there's a problem between your relationship.

Let's just recreate that.

I'm just going to use the drag and drop method, and if we go back now, we should see that that is

corrected.

Okay, So that's just something to look out for with your relationships.

Let's go back into our data and let's say, for example, we were looking at this master table and we

go to each of our employee names and what we wanted to do was actually create a new column for each

of our employees.

And what we wanted to know for each of our employees was what were their total training costs?

And then also we wanted to know how many training courses have they actually been on.

Now you'll remember that previously create a new column.

We use the new column option, which allowed us to create a calculated column.

Now, one of the really nice things about the equals calculate function that we covered earlier is it

allows us to work between using a relationship so we can go between tables.

So let's have a look at an example of that.

Okay, So let's create our first column.

Let's say we're going to create a new column here.

And again, we got now our different formula bar up there.

So we're going to use.

Training cost is going to be our new field name.

I'm going to say equals.

And in this case, we're going to say calculate, because we want to use calculate, because it can

go between the relationships.

So we want to say calculate.

And what we wanted to do is we wanted to sum up the cost that is in the workshop table.

So we're going to say you're going to sum and you're going to use your parentheses, and in this case

we're going to send it across to the workshops table.

And you'll see there that we have the cost field.

So we're going to select that.

So you can see equals calculate the sum of the cost in the workshops table.

Now, just be careful with the number of parentheses.

You'll see that my first parentheses is closing my expression by some expression.

Then the second parentheses actually closes the equals calculate.

So just make sure you got two closing parentheses here.

Once you're happy with your formula, we can press enter on that and you'll now see that the training

cost is now correctly calculated.

So basically it's gone across the workshop table and it's now calculated the total cost for each of

your employees.

Let's look for another example of this.

Let's say, for example, we wanted to know how many courses the people have been on.

So we're going to create a new column again.

So in this case, let's go to actually use these three parentheses, three ellipses, click on the three

ellipses, choose new column.

And we're going to say now that this is the number of courses.

Again equals.

Remember, it's going to be equals calculate because we want it to go across the relationship.

And in this case, what we're going to do is we're going to use a count expression.

We want it to go and count the number of employee IDs that are in the workshop.

So we're going to say count.

And in this case, again, we're going to send it across to count the number of workshops.

So let's say we want to count the workshops table, the number of employee IDs.

I'm going to accept that.

I'm going to have to parentheses close it.

It's a number of courses equals calculate the count of the employee ideas.

I'm going to press enter on that.

So you can see now that it's going to cross and it's now counted how many courses the people have been

on.

I can also see that, for example, where people haven't been on any courses, it's actually just giving

me blanks for that.

There we go.

This person's obviously been on no courses.

Okay.

So that's a really useful function to be able to use the calculator column and the equals calculate

and to be able to then go across.

Also, what I'm going to look at is let's say we were looking at our workshops table.

And one of the things that you might use quite a bit in Excel is called the VLOOKUP.

So every lookup allows you to go to another table, look up a piece of information and return it to

your existing table.

So something that relationships allow us to do is to be able to do the exact same thing.

So let's say within our workshops now we have the employee ID and so it tells me this is employee ID

number one.

Now if I go back to the master table, I can see that one is Gustavo.

Now what I might want to do is actually return the employee name.

And let's say, for example, his department back into the workshop table.

So we might want to have that information over here.

So again, we're going to create a new column because we want this now to return a result for each row.

So we say new column and we're going to say now we want to know what is the employee name.

Now, please note, remember, you can have this same field name, but in different tables.

So we're going to say employee name in this case equals we're going to use a function called related.

So once you use related, it's basically doing a VLOOKUP, it's going to use the relationship and it's

going to go across to the other table and it's going to look up this employee ID and return the information.

So we're going to say related.

We wanted to go to the master table and we wanted to return the employee name field.

So we just choose master table, employee name.

This one's only got one parentheses that needs to close.

And we're going to select related on that.

And you can see now that we return the employee name and correctly we got Gustavo as the employee number

one.

The other one that we wanted to see was the department.

I'm going to use the same, same thing again.

So again, we're going to create a new column and this one we want to say is going to be our department.

I'm going to say equals.

In this case, again, we're going to use related.

And again, we wanted to go to the master table.

And in this case, we're going to return the department again, just one parentheses to close this.

And we're going to press enter.

And as you can see, there's quite a powerful lookup that we can use.

So there we go.

There's some examples that you can use with your relationships to create calculations and also lookups.

We're going to conclude the lesson there.

I will see you in the next one.