This blog post focuses on different aspects of the Alteryx platform that I’ve received questions on while delivering training. Hopefully, this will help answer some questions you may encounter in your analytics work.

Transpose and Crosstab

You can use the Crosstab tool and the Transpose tool to rearrange your rows, columns and cell values in different ways. In a broad sense, use Transpose to move from wide tables with many columns to long tables with few columns and more rows, and use Crosstab for the inverse.

Using the Transpose tool, you can move from showing different information about your Key Fields in different columns from showing the same information in just two columns: one with the name of the value, and one with the actual value:

Using the Crosstab tool, you can rearrange your data to show different information in your Group By fields in individual columns. Note that your data will be “rolled up” to the Group By fields. In the resulting crosstab, each row will be a unique combination of the Group By fields with the data aggregated using the aggregation method you specified in the tool:

Union, Join and Append

Use the Union tool if you have two tables that contain the same columns but different rows, like two tables containing financial data from different months. Use the Join tool if you want to combine two tables side-by-side on a field that exists in both tables, like an ID number or a date. Use the Append tool if you want to turn a single value into a column in a different table.

When you use the Join tool, the resulting table will contain the Join Field that’s common to both tables, along with all the columns from both tables:

Use the Union tool when your tables contain the same information about different observations. The resulting table will contain the same column headers and all the rows from all the tables:

Use the Append tool if you want to perform a Cartesian join, meaning a join without a key field where you just connect every row in the left table to every row in the right table. Most often, you’ll do this to add a single value to a table as a column:

Field Types in Alteryx

Field types, meaning how the different columns are classified in their metadata, play a big role in Alteryx. In a broad sense, there are three different field types: Numbers, text and dates. For numbers and text, there are different subtypes. Those subtypes differ chiefly in the size of the values they can hold. For instance, text fields with long entries need field types that support long words, and numbers fields with decimal points and negative values need field types that support decimal points and negative values.

In most cases, you can change your field types with the Select tool, except for dates, which you can most easily change with the dedicated DateTime tool. Doing this is important: You can only perform mathematical operations on fields that are classified as numbers, and you can only join on fields of the same type.

IF / THEN Statements and the Formula Tool

One common application for the Formula tool is to create a new field based on the data in existing fields. Sometimes, that involves simple arithmetic. Other times, you want to create new values using IF/THEN logic. The syntax for IF/THEN statements is as follows:

IF [Field] = ‘Some piece of text’

THEN ‘Some other piece of text’

ELSEIF [Field] = 14

THEN ‘A different piece of text’

ELSEIF ISNULL([Field])

THEN ‘Missing’

ELSE ‘Text in any other case’

ENDIF

In general, whenever you’re referring to text, place quotation marks around it, but leave those out when you’re referring to a number. [Field] = 0 evaluates whether the content of the field is equal to the number 0, while [Field] = ‘0’ evaluates whether it’s equal to the word 0.

Conclusion

I hope you’ve found these explanations helpful. Feel free to reach out if you have any specific questions that weren’t addressed here.