Questions from Alteryx Training

Data

Questions from Alteryx Training

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:

transpose tool in Alteryx

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:

group by with Crosstab in Alteryx

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:

join tool in Alteryx

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:

union tool in Alteryx

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:

Append tool in Alteryx

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.

More About the Author

Chris Steingass

Analytics Consultant
Building Signal-Based Models with Cross-Validation One challenge in building analytical models involves separating signal from noise. Signal describes the patterns we consider “real” in ...
P-Values and the Reductio Ad Unlikely Relying on p-values to reach business decisions is common, which is reason for concern. Conducting analysis that’s too heavily based on ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072