Let's start our case. The first thing we
need to do is to load the data.
The case includes two separate datasets,
so let's load those into Alteryx.
Then let's see what we can learn about them.
We'll bring in a Browse tool from the Tool Palette
to look at them and view them and see what we can learn.
Let's bring both of those in,
there's two separate ways to bring data in.
First, I can just drag it in,
so I'll go off-screen and go
ahead and grab that first file.
This is pcard_data_part1_v01 it's a CSV file.
I'll just drop that in and you can see
the names there and it's all ready to go.
Secondly, I can go up to the Tool Palette and I can get
the Input Data tool right here and pull that down,
and then I can go over to
the Configuration Panel over here
and find the path that I want and pull that in.
I have it just right here because I've pulled in earlier.
I can do that.
Once I click off of it,
you'll see the names there and it's all ready to go.
I have both of my two files that I need to do,
and I'm going to go ahead
next and I want to look at the data.
There's at least two ways to do that as well.
First, I'll go get the Browse tool from
up in the Tool Palette and I'll drag that over.
That will allow me to look at
the spreadsheet once it comes in.
I'll put it on both of these, I guess.
I'll do that here.
I'm going to run things and
we'll show you the two ways to look at this.
I run it everything looks good.
I don't see any errors down in the results window.
Let me look at my first Browse tool,
and I've got a couple of things I can look at.
On the left, I've got a couple of
different ways here in
the configuration window to look at the data,
and so it's going to give me each of the columns here and
give me this nice summary information
about it so that's pretty cool.
That's nice to use. The other thing
I can do is I can click on the
"Output Anchor" here so
I can look down the results window,
and this gives me every column.
I can look here and I look
through each column and it gives
me a little bit of data there for each one
so that's also useful.
Of course, I can click up here at the "Messages" area of
the results window to see that there are no errors.
If I go back to the Output Anchor,
I can see the number of rows that were brought in,
and it only shows some of
those rows just for size limitations.
But I can also get Metadata over here,
and that's also helpful.
What I can see as it's brought in strings,
it brings everything is in a string,
so we'll have to deal with that
here in a bit as well, of course.
What we need to do next is we'd like to join
these two different datasets together.
There's multiple ways to join data together.
If we go to the Join area up here in the Tool Palette,
we can see several,
and let's go ahead and look at
Join which sounds like exactly what we want to do.
Because we're trying to join things together.
This has combined two data
streams based on common fields.
Normally what we have here,
when we use the Join tool,
is we have two separate datasets that
have one common column,
a primary key that is the same again in both datasets,
but then different columns in each dataset.
We bring them together, merge or join
is what we would call it in R or an SQL.
Then if we look at Union, here's our other options.
This combines two or more data streams with
similar structures based on field names or positions.
The Union then is just concatenating the data.
Here we want to stack
rows together one on top of each other,
or stack columns one after the other.
This is what we want and why?
Well, if we look down in our data,
I'm going to just pull this up,
we can see that we have a case, here we go,
where we have, if we
click on "Data" in the results panel,
we have 10 different columns,
agency number, agency description.
If we look at that and then we
go up and look at the first one,
we can see that we have the same columns.
It looks like the data is just split in 1/2.
What we want to do here is
stack the rows on top of each other
because they have the same column names
and they'll just stack together,
and that is the Union tool.
We bring the Union tool down and we're going to drag over
a connection here and drag over a connection here,
and just go ahead and hit "Run" and see what happens.
That runs through.
We looked down at the results window
and we see no errors.
But we do see this yellow caution.
Says the field MCC is not present in all inputs,
and likewise, field
MCC.Description is not present in all inputs.
We do have two warnings and that's problematic.
Let's click on the "Results Anchor".
Let's look through and sure enough we see
that the first problem because we have 11 of 11 fields,
and again, we want 10 fields.
We're stacking together the rows from
10 columns. That's not what we get.
We have a problem and as we scroll over,
we see the nature of the problem as we
look at each of these the combined results here,
we see that we have MCC.Description,
but then we have MCC.
This was the warning we got before,
and we can see that this column is null here.
If we had all of the data,
we would notice that MCC is null as we go further down.
The MCC column is misnamed.
It's named one thing in one of the datasets.
In the 1st data set, it's called MCC description,
in the 2nd dataset, it's called MCC.
We just need to rename those to
one common thing and we will be able to stack the data.
The next thing for us to do is to fix that column.
We want the columns to have the same name so that we
can bring in these two datasets together.
Probably the easiest thing for us is to go
up to our first one,
and then we can change that one to be,
let's see, we have the MCC.Description column.
Let's just change this to be MCC.
Then we'll have MCC there,
will have MCC here,
and everything should stack together.
We should have 10 of 10 fields
and everything should work.
Let's delete this union
here because that one didn't work.
Then, we need to change
the name from that first data set.
Let's go up to the Tool Palette,
and look under Preparation.
It's not immediately clear which of these is going to
be the best thing to use to change a name.
One might be Data Cleansing,
this is not going to work.
It says perform basic data cleansing operations such,
none of that says changing names.
The right tool is the Select tool.
Here if we read the Select tool,
it says select,
deselect, reorder, and rename fields.
Perfect. That's what we want.
Let's drag this guy in and we're going to put it here,
I'm just dragging it over a little bit.
If we go over here to the configuration panel,
we can see that there's this rename column.
This couldn't be easier.
All we have to do is go down here,
find our MCC.Description and we're going to type in MCC,
and we're all set and that should actually work.
Let's go ahead and run that and see what happens.
We've run it, we click here,
we've got no errors or messages,
and we can look at our Output Anchor.
Clicking here the "Output Anchor" and now we have
that first data set renamed MCC as we would expect.
That's excellent. All set there.
Let's go then to our Join
and we'll pull down our "Union",
and we want to union these two together,
so we take that dataset,
the first one cleaned at now,
and we take our 2nd dataset and let's run that.
Let's look at the output here.
We click on our "Output" tab on both.
We were looking at our Output tab we've
got 10 of 10 fields,
which is exactly what we want in columns.
We've got our MCC column and no
weird MCC.Description null or whatever.
That looks great. We've got 126,077 records.
Well, is that right?
Let's see, we have 63,038 for our 1st data set,
we have 63,039 for our 2nd data set,
and if we do the math, that gives us 126,077.
Everything great so far we've brought
our two datasets together correctly.