7. Record ID and Cross Tab

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

In this tool lesson, you will learn how to uniquely identify your records, and to pivot your data for appropriate viewing.

Lesson Notes

Record ID

  • The Record ID tool attaches a unique identifier to every data entry in a dataset

Cross Tab

  • The Cross Tab tool is similar to the Pivot Table function in Excel, allowing users many different ways to combine, manipulate, and display data

Transcript

Once we've connected our dataset, verify the contents using the select icon and sort it in rename the various fields.

It's time to start manipulating the data.

A good first step in this process needs to check that our records have a unique identifier.

For most datasets in Alteryx we'll typically want to join a column to other datasets.

In other words, will want to combine data from two different sources.

We'll discuss the mechanics around joining datasets in a later lesson, however, know that this process can quickly get messy if you can't trace back to the individual records.

If your dataset does not have a unique identifier, the record ID tool will take care of this by creating a column of sequential integers.

Lets add a record ID to our dataset now.

We'll navigate to the preparation tab, bring down the record ID tool, and connect it to our workflow.

As we can see, in the configuration window the record ID tool includes options to begin labeling our different starting value, change the type of the label field and even change the field position.

In this example, I will create a simple record ID column that uses the sequential number beginning with 1, starting with the first row.

We'll run the tool and see the new record ID column to the left of our data in the output window.

With the record ID now in place, our dataset is ready for some analysis.

From here, I'd like to create a table that shows daily sales of each product.

In Excel, we would use a pivot table to create this output, but in Alteryx, there's a tool called cross tab.

Let's look at in an example.

We'll go to the transform tab on our tools palette and connect the cross tab tool to our workflow.

Moving onto the configuration window, we see entries for grouping data, new column headers, values for new columns, and method for aggregating values.

Don't worry if you're a bit lost by the terminology.

It will make more sense once we select our data and run the tool.

Since we're looking for daily sales, we'll want to group the data by date.

Since we're looking for sales by product, we'll want additional columns for each of the different products in the data set.

As such, I will select a product name for new column headers.

As mentioned previously, we're looking for sales.

So, we'll enter that into the values for new columns field.

Finally, Alteryx needs to know what to do with those sales figures.

Since we want total daily sales numbers for each product, we'll select Sum.

We'll now run the tool with CTRL+R.

In the output window, we can see the dates on the left side followed by the 4 product columns with our daily sales values.

As we can see, this tool is similar to Excel Pivot Tables in both function and output.

As with Pivot Tables, this tool can be very powerful.

Offering many different ways to combine, manipulate, and display data.

While the record ID was not part of the output for the cross tab tool, it's important to ensure that your data has unique identifiers.

As our workflows get more complicated, these identifiers will help us trace data allowing us to easily find inputs for various analyses, or the source of calculation errors.

In the next lesson, we'll continue with the data manipulation options available in Alteryx, focusing on the filter and summarized tools.