[MUSIC]
All right, so our next task here is to continue cleaning the data.
And if we look at the combined data set,
we see that we have two different agencies here.
So we have the University of Oklahoma, and we have Oklahoma State University.
Now, this case is about Oklahoma State University.
So we don't want the University of Oklahoma.
So what we need to do is filter that out, right?
Or filter in only the Oklahoma State University.
We also might notice that Oklahoma State University has agency number 1000.
All right, so if we see that,
we have a way to then get rid of the University of Oklahoma.
So we need to filter this.
If we go to our preparation tab,
we see a very helpful looking tool in the palate, and that's the filter tool.
Now, if we read through this description, that doesn't seem particularly helpful.
But that is indeed what we want.
Let's pull this over, and we're going to filter.
And what we're going to filter then.
If we go next to the configuration, we see this nice little basic filter that's
already preselected for us, and it tells us to select a column.
So let's do that.
Let's make it easy on ourselves by selecting Agency Number, and
we want that equal to 1000, right?
So equal to 1000.
Pretty simple.
Let's go ahead and run this, and see if that works, [SOUND] okay?
Now, we have two output anchors here.
We have a true and a false.
So the true is going to be if that statement is true.
So this should be just Oklahoma State University,
where agency number equals 1000.
And sure enough it is.
Just glancing through, we're only looking to subset of the data, but
everything looks really good there.
All right, if we click on false then, this should be when that statement is false.
So this should be not Oklahoma State University.
And the idea is we look at that output anchor,
we can see under the false here, right?
That we have University of Oklahoma.
And so it looks like that worked so far so good.
So our next task is to go in and to make sure we have the same case, upper case or
lower case for all of the columns that we expect need to be strings.
So we have several columns.
Some are definitely numbers such as amount, and some are definitely text.
So one problem we might have and that we might see here is that we have
different uppercase or lowercase mixed together in these files.
So some here, this is sentenced case, this is all upper case.
We need to have that be consistent.
So all of our text columns need to definitely be the same uppercase or
lowercase.
Some tools will treat a name, for example, Roundtree here as two
separate names if it has different case, and so we need to fix that.
So there's a couple different ways to do this, I'll demonstrate too.
If we go back up to preparation, the first tool in the palette that might strike our
attention here, that might grab our attention is data cleansing.
So this sure enough, it says, that does a lot of things, but
it also modifies capitalization.
So it's going to make the text uniform.
So let's bring this in, and we'll pull over again to true, right?
Because we're keeping the true data, we're going to ignore the false.
We only want Oklahoma State University.
And if we look at this tool in the configuration area,
we have a bunch of different things we can do.
But one thing is we don't want to select everything,
we just want to select the text one.
So we have agency description, cardholder name,
item description, merchant, and MCC.
So let's pick those.
Those are text files, the files that we want to end up being strings.
And we're not going to do anything with nos.
The only thing we're trying to do is change the case.
So we only have that checked, and let's go ahead and make it upper case.
So that is one way to do this.
Another way is to use, a slightly more complicated way but a very powerful
tool we could use to change this type of case would be the formula tool.
You see here, as we click on it in the palette,
this is performing a broad variety of calculations and operations.
So let's drag this over, we're going to take from True again, of course.
And as you can see over here in the configuration area,
this is a little more complicated, right?
We're going to have to first select the column, and then we have to
select the expression of the operation that we're going to do for that.
And if we click on this operation thing right here, this f(x) function formula,
we can see that uppercase does show up in our suggested for this data type,
that's what we're going to use.
If that doesn't show up there for you, you can go down to string, and
because we're operating on strings here, and go down to find uppercase.
So here's our function, do an upper case on a string.
And so the only thing that we need to do next then is to type in the column number.
The column is surrounded by square brackets.
So here, item description is what we want, and then we're done.
So that's our first one.
And we just have to repeat this process for
each of the different columns that we're going to use.
So there's agency description that we just did.
Let's do cardholder name.
I'm just going to paste this in here, and
then change the column because it's actually pretty easy to do that.
And so the cardholder name.
So if I just type of seeing here, it shows up cardholder name.
And then you'll notice the nice preview that we get.
So we can kind of double-check that everything's working as it should.
The next one we'll go through and do is item description.
And again, I'll highlight this, and just type an I, and item description comes in,
and I'll click on item description.
Couple more here, item description.
Next, we have the merchant.
So let's do that.
And again, I'll just paste, and I'll type in an M here,
[COUGH] and change this variable name.
There's merchant.
Again, our previews all look good, and we'll do MCC, which should be next.
MCC, And that's it right there.
So we've got 1, 2, 3, 4, 5.
They all look good.
We all see nice previews.
Let's go ahead and run this to make sure it works.
[SOUND] All right, so if we click on the output from the data cleansing tool,
right?
We've got 10 of 10 fields.
if we want, we scroll, and look at everything.
All those texts seem in upper case.
So that looks great.
If we go over and we click on the output for our formula, again, same thing.
We've got 83,126, 83,126.
And again, if we click on our output anchor there,
we can see things look really good.
Everything is in uppercase just as we expected.
So it seems that using both these tools worked, well and
we're able to demonstrate both.
So the next thing we want to do is continue cleaning the data.
As you can see, if we look down at the data that we have now in the metadata,
everything is still a string.
Now, certainly, some of these are not strings, and they should be numbers, and
some should be dates.
So let's do three more tasks.
First, let's go in and eliminate columns that we don't need, all right?
That don't have useful information.
Second, let's go in and change to numbers columns.
It should be numbers, and change the dates columns that should be dates.
We want to do this.
The select tool is probably the best one for this.
We've used this before to change names, and it'll work for this as well.
Great tool,
you can see over in the configuration area that we have a lot of great options.
This is what we did before to rename the column.
Let's get rid of this unknown thing, which isn't really a column but
we don't need it, and let's get rid of project column, which is right here.
[COUGH] Is not at all useful, and has all in a, okay?
So we unselect project in unknown, and
then let's go in and look at, then change our things in numbers.
So we've got agency number, which is an integer, so there's no decimals.
So we can pick one of these integer options.
Any one of them is going to work.
That just allows you to have bigger size numbers if you go to integer 64 versus 16.
We'll just pick 32, it doesn't really matter for us.
Next, let's go in, and we definitely need to change amount.
We're going to use that quite a bit.
And so that is a decimal.
And so there's different ways to represent a decimal.
We can do flow, we can do double.
Let's use double.
That's kind of the standard, and give us the most options.
So double is what we'll pick because amount is a decimal.
And so there's our amounts.
And then we just need to change dates.
Again, there's multiple options too.
We have two columns, their dates, and there's multiple options.
Let's just take the simple one here, date.
And date, and we'll run that.
And we look then at the output anchor, and we look at the metadata,
and we can see that things do look appropriate as they should,
and if we look at the data, everything looks great so far.
So done with that task.
Okay, so for our last cleanup task,
we're going to take the date column that we have, and to create three new columns,
these are going to be really helpful for analysis later.
And so let's use the transaction date column because that's the date that
the transaction is actually happened.
And what we want to do is create three columns.
The first will be a column that list the month of the year.
The second will be a column that lists the day of the week.
And the third will be a column list the day of the month.
And so we're going to do that again with the formula task.
This is the one that's going to require us to write formulas for these.
And so let's go over to the configuration area, and we have this nice setup,
where it gives us the option to add a column.
And so let's call this first one the month.
This is the one where we want to list the month of the year, right?
So this should be 1 to 12 for the first January to December.
And so let's go ahead and try and see if we can find a function or formula for
this, and there's nothing good in the suggested area.
So let's go down to DateTime, which looks very useful and helpful.
And let's see.
If we go down to DateTimeMonth, we see that this return's a numerical value for
the month of the datetime value, which is exactly what we want.
The datetime value here are the column that we're working with,
is going to be transaction date.
So there we go, and the preview looks good.
We're all set there.
Let's go and create our next one.
This second column that we're going to create,
this is going to be again the list the day of the week.
So let's call this weekday.
And again, day of the week.
This is going to be 1 through 7.
And so let's look at that function and see if they have anything.
And I won't waste your time, they don't have it.
So this is something we're going to have to write on our own.
And so what we need to do then is we need to use the generic version that they have.
This is the DateTimeFormat.
This is the generic function that allows us to do multiple different things.
And so we'll click that, and again,
we'll put in our column transaction date again, okay?
And then this f is where we select what we want to happen here.
And so here, I want to take percentage sign, w, and
this is going to give us what we want.
You can see so far, it looks good, and this one preview, it gives us, and
we'll double-check it when we get to the end.
And then we select our last column, and that's going to be,
let's call this DayMonth.
This again is a column that lists the day of the month.
And so this is going to be 1 through 31.
And so again, we go down to DateTime, and we say, DateTime.
And this one looks good.
So this returns the numerical value for the day of the month.
Exactly what we wanted, right?
So we take DataTimeDay, and
we again just add transaction date in there.
And again, the preview looks great.
And so let's go ahead and run everything, and we get no errors.
And so that's great.
And so if we look at the output anchor, let's look at our new columns and
just kind of see if they make sense.
So here's the transaction date is 9 28 2020, so the 28th of September, 2020.
And so the month is 9, that's what we want.
The weekday is 1.
We wouldn't know that, but it looks like it's working correctly.
And then the day of the month is 28, which is exactly right.
And if we scroll down, we can see that this looks to have worked correctly.
So excellent.