Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Videos

Get your skills in gear with hours and hours of videos on topics ranging from beginner to advanced

Alteryx Tips and Tricks: Data Wrangling

mikecusic
Alteryx Community Team
Alteryx Community Team

Continuing with our Alteryx Designer Tips and Tricks series, here are some ways to quickly get your data ready for analysis.

 

 

Convert Results to Text Input

 

 

Sometimes it is helpful to create a sample of your data for developing workflows and processes. Did you know you can copy a sample of data from the results window and turn it into a text input? To make the new input, select the desired data in the results window, then Right-click and choose "Copy Selected Cells With Headers". Then, Right-click on the canvas and choose paste to create a text input.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Create text input from Excel

 

 

Need to copy records in from Excel without bringing in the entire file? Highlight the cells in the Excel file and copy them. Right click on your Alteryx Canvas, and select "paste" to paste your data with headers.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Forget all Missing Fields

 

 

The Select Tool remembers fields that used to be included in the data but are now missing because they were removed upstream. This is a useful feature when building workflows to help you troubleshoot and make sure all data flows through. To clean this up, use the “Forget all Missing Fields” option in the Select Tool.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Filter and Sort Data in the Results Window

 

 

Hover over a field name in the Results window and click on the three dots to the right to open the filter and sort options. You can sort the column ascending or descending and filter the data to narrow it down to only relevant records (e.g. "is not null"). To remove a filter or sort, click the X to the right.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Easily Compare Records

 

 

Highlight the desired records with Ctrl + Click or Shift + Click. Then, open a new window with just the selected records by clicking on the “+” Icon from a Browse Tool and selecting New Window (Selected Records). You can also export the selected records to be used as an input file in a new workflow.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Quickly Remove Nulls

 

 

To change all null values in a numeric field to zero (e.g. to make calculations easier), use the ToNumber function in the Formula Tool: ToNumber([Field1]). To change multiple fields at once, use the Multi-Field Formula tool.

 

Check out more Tips and Tricks on the Alteryx Community!

 

IFF vs. IF/THEN Statements

 

 

IIF is a shorthand version of a single condition test. While a simple IF/THEN is a typical way to do this, you can also create a Boolean test within the expression. It works very similarly to the IF function in Excel, but the Boolean IIF function can be much quicker to write out.

 

Check out more Tips and Tricks on the Alteryx Community!

 

Save Custom Expressions

 

 

Have a frequently used formula that you seem to rewrite over and over again? Don’t forget you can save expressions in the Formula Tool. When you are ready to use it click the open button in the Formula Tool. Note the open button also loads a list of recently used expressions!

 

Check out more Tips and Tricks on the Alteryx Community!

 

Dynamically Identify the Number of Columns to Parse

 

 

Do you need to split your data into multiple columns but aren’t sure how many columns to split it into? The below workflow helps you determine this dynamically.

  1. Add a RecordID to be able to identify rows of data later
  2. Split your data into rows (instead of columns)
  3. Use the Tile tool to assign tiles for each column. Using RecordID as the unique field will assing “1” for the first column for each record, “2” for the second, etc
  4. Use the Crosstab tool: group by RecordID to uniquely identify each record, Tile_SequenceNum is the header and the values are the data
  5. Sort your dataset by ascending RecordID
  6. Use the dynamic rename to replace the number from the Tile_SequenceNum with your header

by: ESTERB47

 

Check out more Tips and Tricks on the Alteryx Community!

 

 

Videos produced by @mikecusic  and @MaddieJ . Special thanks to @MargaritaW , @JessicaS  & @HenrietteH for these original tips and tricks.