Original subtitles

Welcome to this lesson.

So you've had a discussion with your human resource department, and they actually really like this

table that you've just produced here.

And what they would like to do is to actually produce a summary table that's just got this data in it,

maybe a couple of extra pieces of data.

For example, they would like to know what is the total number of sick leave hours by each of the job

positions and what is the vacation hours.

So let's say we wanted to create this as a separate table, as a summary table that we could then use

within our reports.

Okay.

We're going to go back to our transform data.

And what we're going to look at is how we create this data now and how we could aggregate it.

So we've created a query, and this is called Employee Master.

If I right click on this, it then allows me to be able to have options as to what I want to do with

this query.

Now, two of the important options are called duplicate and reference.

If I use duplicate, what it will do is it will create a new table and it will duplicate all of the

applied steps that the table has.

But it will only duplicate the applied steps as at the moment in time.

Later on, if I go back and I make changes to the source table, it will not actually update the new

table with those changes.

However, if I use reference, what it will do is just reference this table and if I make any changes

to this table, then it will.

It will use those changes with the new table.

So in this example, we're going to use reference.

So I'm going to say we're going to reference the table.

As you can see, I've now got a new query that is being created.

However, it's only got one step, which is my plot step source.

If I go back to my advanced editor, you'll see that the source is the employee master.

So basically, at the moment, all we're doing is referencing that I'm going to change this name, so

let's call this our summary.

Okay, So that's our summary table, right?

So what we want to do now is we want to change this so that we're going to see our job position and

then we're going to see that information that we were just talking about.

And what we're going to do for that is we're going to use a function called group by.

So group by is really great.

When you want to create summary tables, you want to see key metrics, You want to see your key performance

indicators, for example.

So we're going to select group by and you can see that we get two options for group by.

We've got basic and advance.

We're going to jump to Advanced, which just allows us to be able to choose multiple fields.

Now the first one is what are we grouping by now?

In this case, we actually want to group by job position, and I'm actually going to add another grouping,

which is our gender.

So we're going to say we're going to have job position and our gender as our two groupings.

It then allows me to add different aggregations down here.

So let's say that the first one I want to know is what is my average age for each of my job positions

and genders.

So my operation that I'm going to perform on this is an average and I'm going to choose my column.

And what we're going to say is that we actually now want to be able to do this on my age.

So I'm going to pick my age column.

I could also add a new aggregation unless I want to know what it is the.

Highest.

Just correct that.

And we're going to say operation on this one is going to be a max.

And again, we're going to do it on our edge.

Could also say what is the lowest eight?

So I think you're getting the idea on how this works.

We're just basically putting a column name in or picking a way that this is going to operate.

And then we're choosing what is the field that we're going to do and just see if we can move this up

a little bit.

Okay.

So we just moved it up a little bit.

There we go.

Now you can see the fields a bit better.

I'm going to pick our age again.

So we've got our average age, high stage, low stage.

Let's say we want to count how many people we've got so we can say a number of employees and we're going

to count the rows here.

So we don't need to pick a particular column and add another aggregation.

And we could say that we wanted to know what is the number of vacation hours.

So we're going to say number of vacation hours.

So field name, in this case, we would use a sum and we would then be using our vacation hours.

And let's say the last one is on number of sick leave hours.

Okay, So there we go.

Some Now we're going to pick our sick leave hours.

Okay.

So I'm going to click okay on this and you're going to see quite a transformation happen.

So basically, the table now has changed to show this grouped rows that I have now set up.

So you can see that you've got your job position, you've got your gender, and then you've actually

got the results of all those calculations.

So this is now showing in that table view the results of that.

If it didn't time you wanted to go back and you wanted to make changes, you could click on the icon

and you could just go back in here and you could make any changes that you would want.

Guess I'm happy with that.

We're going to close and apply and we're going to now take this data back into power by and you can

now see that I have a second table, which is my summary table, and I have all that detail.

So if we delete this now, let's just say we create a new table.

We'll just collapse that and create a new table.

And.

Again in this case.

Now, we wanted to see all this just do a bad job position and then you could just drag these fields

in that you wanted to see.

Again.

You can see the average age now it's sundered.

So we would actually have to average that out.

Lowest wage.

See again summed it so we'd now say that we would want to know the men.

Had a pretty easy to change number of employees.

There we go.

Number of sick hours.

And you've now got your new table from your new summary table.

As I said, basically, I just wanted to show you an example of using the group by option and just showing

you how you can easily aggregate data and change the tables into new types of tables that just show

that summary information.

Okay.

We're going to conclude the lesson there.

I will see you in the next one.