Much like Tableau Prep, Alteryx is an ETL tool that functions primarily through drag-and-drop rather than from the code line, visualising the final workflow as interconnected steps. While Alteryx is overall a much more powerful tool, allowing for more complex workflows, most simpler ETL tools have equivalents in both softwares. In this blog, I'll run through a few Alteryx tools, broadly explaining what they are and what the equivalent step would be in Tableau Prep. For more in depth explanations of each tool, you can refer to the official documentation.
Input
As it sounds, the standard Input tool in Alteryx allows you to input your data. This works pretty much the same as the input step in Tableau Prep, except for a few options that are different in each (e.g. you can specify the Code Page in Alteryx – useful if you have string data in a non-Latin alphabet).
Similar to Tableau Prep, you can bring in multiple files at once: if they are in the same folder, you can change the file name in the file path to * to select all files with that extension in the folder, which will perform a wildcard union; you can optionally tell Alteryx to input the file names as a new field.
Browse
The Browse tool in Alteryx allows you to view and explore your data. In Tableau Prep, you would normally need to open your data in a Clean Step, which would allow you to get profiles of your fields, e.g. the min and max values, etc. It caches data to view (which is quite performance heavy, so should be removed from your final workflow) and can also render maps and charts.
Select
Alteryx's Select tool is where you can edit the data type of your columns, the lengths of fields, the names, positions, and which (if any) you would like to remove. All of this can be done in a clean step within Tableau Prep.
Text Input
This tool allows you to manually input data within Alteryx itself, which is physically stored within the file. This functionality isn't available in Tableau Prep: you would have to create an external file and import it instead. This is very useful for creating static dimension tables or dummy variables on the fly!
Output
As it sounds, this tool outputs data from Alteryx – no prizes for guessing what the equivalent tool is in Tableau Prep. Within this tool, you can choose to make a new file, overwrite an existing file, append to an existing file, or add a new sheet to an existing Excel file. Note: like in Tableau Prep, the output will only be saved from Alteryx once the output tool is run.
Data Cleansing
Within this tool, we can remove unwanted characters from our fields, like leading or trailing spaces, puncutation, letters, numbers and nulls. However, you cannot perform different operations on different fields within the same Data Cleansing Step, e,.g,. if you choose to remove all letters, it will do that from all selected columns. This is quite different to a Tableau Prep clean step, in which you can do all of these things independently within a single clean step.
Text to Columns
Here you can split the contents of one column into multiple columns. You can specify a delimiter by which to split the data, as well as the number of columns you want the data to be split into. This is equivalent to the "Split Values" functionality in Tableau.
Unique
The Unique tool is used to filter out duplicates within a table. You can specify the field(s) you want to make unique (e.g. your assumed row level). Alteryx will output the unique data from the "U" and the duplicates from the "D". There is a find duplicates function in Tableau Prep, but word on the street (aka multiple people at TIL) is that it's significantly clunkier than its Alteryx equivalent. If you want to do the same in Tableau Prep, it might be easier to just aggregate by your unique field(s) instead.
Filter
Splits data based on a boolean condition into TRUE and FALSE. This can either be a basic filter using a single field (e.g. [Field] > 10) or a calculation, which could have multiple conditions. The options for the basic filter are data type dependent: a string cannot be greater than another, but it can contain a substring.
Formula
This tool allows you to either update an existing field or create a new field based on a written calculation, similar to calculated fields in Tableau Prep. Many Alteryx functions can be used within this tool, which I won't go through here – see the Alteryx documentation to find all the functions available in Alteryx. But a word of warning for those used to Tableau: the syntax has quite a few differences, so be sure you're using the right functions for the right software!
Sample
Using the Sample tool, you can takes a sample of your data that fits a certain condition, e.g. Top N will take the first N rows. Within this tool, we can also group by different dimensions, e.g. finding the Top N per category. This is a little more complicated in Tableau Prep, where there's no dedicated tool to do this. Instead you might generate a rank (per category, if so desired) and then filter that.
Sort
As the name suggests, this tool will sort your data by a specified field. It can sort strings by alphabetical order, and numbers numerically, either ascending or descending. There isn't a specific tool for this in Tableau Prep; you can just click the sort icon on a column to sort it within a clean step.
Union
The Union tool in Alteryx is equivalent to the Union step in Tableau Prep. Like any union, this stacks tables on top of each other. But unlike in Tableau Prep, within Alteryx you can specify how you would like Alteryx to stack the fields: by name, position or manually (user specified). This is very handy for when we want to union tables with mismatching fields; in Tableau Prep, which would have to be done in a separate step prior to unioning.
Join
The Join tool can join two input data sources together, much like a Join step in Tableau Prep. You can specify how you would like Alteryx to match the fields from the two datasets: by Record Position or Specific Field.
So far so similar, right? But while in Tableau Prep you specify within the step what kind of join type you would like (e.g. Left Outer, Inner, etc), the Alteryx Join tool separates the join results into L (left outer; left table that doesn't match right), I (inner; matching data) and R (right outer; right table that doesn't match left). If you need a full left, you need a separate Union step to bring the to left and inner outputs together. Within Alteryx's Join, you can also choose which columns to keep and remove and rename them if desired – similar functionality Select tool – while this would have to be a separate clean step in Tableau Prep.
Append
Similar to a join, the Append tool allows you to add fields from a Source table onto a Target table. But unlike a Join, where you specify a join condition (e.g. field 1 = field 2), this tool will match every row of the target table to every row of the source table. This is liable to explode your data: if the Target and Source tables both have 100 rows, your Append result will be 100,000 rows. By default, Alteryx will prevent you from appending more than 16+ rows, but you can change that in the tool's settings. This is very useful for calculating percent of total: you can sum a field and then append it back onto the original table, which will allow you to perform a simple row-wise calculation to get the percentage.
There is no dedicated Append tool in Tableau Prep, but similar results could be achieved through a join. Note: this is not to be confused with Power Query's append tool, which is their word for a union!
Summarize
Alteryx's Summarize tool is equivalent to an Aggregate step in Tableau Prep. Here you can perform aggregations – both numerical, like SUM or MAX, or even concatenating strings, as well as specific Financial functions like Net Present Value (NPV). Tableau Prep has fewer aggregation options, but it works pretty much the same way.
Transpose
While in Tableau Prep the Pivot step allows for both rows-to-columns and columns-to-rows, these are separate steps in Alteryx. The Transpose tool pivots columns to rows. Your key columns are the columns you would like to keep as vertical fields, while the data columns contain the data you want to pivot. Fields cannot be both: selecting a field as a key column will automatically deselect it as as data column, and vice versa. This is slightly different to Tableau Prep, where you just drag all of your data columns into the "Pivoted Fields" section.
Cross Tab
The Transpose tool is rows-to-columns; this is instead columns-to-rows. In this tool, you must specify what the data should be grouped by (aka which field(s) you want to keep as rows), the field containing the new column headers, and the values that should go in the new column. We also need to specify how to aggregate the data in case of duplicates on your grouping variables. The options here are different for numerical values fields (e.g. SUM, MAX, Average, etc.) and string fields (concatenate, first, last).
Note: you cannot cross tab more than 32000 columns withou Alteryx being very upset with you – but hopefully you won't even come close to hitting that limit!
Multi-Field Formula
This tool allows you to affect multiple fields within one step, for instance subtracting one field from multiple different fields. The fields must be of the same broad data type, e.g. Numeric. In the formula box, you can refer to aspects of your collection of fields in specific ways, surrounded by _:
- _CurrentField_: uses the selected field in the function
- _CurrentFieldName_: uses the selected field name in the function
- _CurrentFieldType_: uses the data type of the selected field in the function
This then allows you to perform the same action on multiple fields at once. In Tableau Prep, you would have to do this individually.
Multi-Row Formula
While the Multi-Field Formula tool works horizontally (across fields), this one works vertically (down the rows of a table). Using this tool, you can build formulas that allow you to reference the previous (Row-1:FieldName) or next row (Row+1:FieldName) of your table, on any field specified. This has similar functionality to table calculations in Tableau, which are in Tableau Prep as of 2023. One specific use case for this tool in Alteryx is filling down data, which is a clean step option in Tableau Prep; however, most complex formulas do not have a more straightforward Tableau Prep equivalent.