This post contains a breakdown of the Preparation Tools required to pass the Alteryx Core Certification. There are 10 Preparation tools featured within the certification.
Data Cleansing
The Data Cleansing tool can be found in the favourites tab as well as the preparation tab. It allows for the removal of null rows and columns or replacing nulls within data with blanks (for strings fields) or zeros (for numeric fields). It can also remove unwanted characters such as whitespace or numbers/letters/punctuation. Finally, it can also be used to modify the text case of fields.
Filter
The Filter tool can be found in the favourites tab as well as the preparation tab. It performs basic data separation based on simple or constructed boolean formulas. Such as "greater than (x)" or "Does not include "Scotland". This will separate the data into two streams based on whether it adheres to the separating argument, each can be accessed from a true or false output.
Select
The Select tool allows you to exclude columns from your data stream. It also allows you to change their order, file type, or data length. Here you can also rename a field heading or provide a description for the metadata.
Select Records
The Select Records tool allows the user to capture and seperate specific rows of data, whether this is individual rows or a range. This allows the user to error check data, or can be used as a static filter tool. However, this tool does not return the rows that do not match the filter, only those that do.
Unique
The Unique tool allows the separation of duplicated rows, based on one or more column values. The Unique tool produces two separate output streams, one with Unique, and one with duplicate data.
Sort
The Sort tool allows for the ordering of records based on the values in one or more fields. You can also operate with nested sort arguments.
Sample
The Sample tool limits the number of rows based on a selected operator, whether you want to see an exact (n) amount of rows, a random amount of rows, or a percentage of rows. You can also group by column using the Sample tool, returning a number of rows per group.
Formulaic Tools
Formula
The Formula tool can be used to create new, or update existing, columns (note: it cannot update existing column data types). You can create many formulaic arguments with a single formula tool - using a coding language in a form of "VisQL", a SQL spin-off. Some formulas work on a "0-based system", such as GetWord or GetString, however, not all Alteryx formulas will. It is important to check your results in the "Data Preview" section of the workpane.
Multi-Field Formula
Can be used to create or update multiple fields based off of a single expression. Can be used similarly to the Formula tool, but affecting several columns at a time.
Multi-Row Formula
The Multi-Row Formula can be used to create or update a single column, referencing multiple columns in rows that come before or after the target row.
Difference Between Multi-Field and Multi-Row Formulas
One key difference between these two tools is that the Multi-Row Formula tool operates on a single field of data, while the Multi-Field Formula tool can operate on multiple fields of data at once.
For example, suppose you have a dataset with three fields: "Field1," "Field2," and "Field3." If you wanted to apply a formula to each row of data in Field1, you would use the Multi-Row Formula tool. If you wanted to apply a formula to Field1, Field2, and Field3 simultaneously, you would use the Multi-Field Formula tool.
That provides a brief overview of the 10 tools within the "Preparation" category required for the core Alteryx certification.