Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
If you are like me when I first got started, trying to master all the individual tools that are available is a lot like climbing up a hill in a snowstorm during an avalanche! There is just so much to learn and unfortunately The Matrix wasn’t real enough of a movie where I could just download it. (Such a bummer right?) However, as I talk to more and more Alteryx friends that have been around for a good number of years, I started to notice that they spent less time studying individual tools versus learning how combinations of tools can provide useful solutions. Now, thinking back over the 7 or 8 years I have been using Alteryx, I realized that I have just sort of memorized certain combinations of tools to solve certain problems.
So with this front of mind for this blog post, I will share with you five useful design patterns that will help you build out your tool box so that you can shorten the amount of time it takes to provide valuable solutions. This is by no means an exhaustive list, as there are potentially an infinite combination of tools and techniques that one could come up with but these are ones that I have used hundreds of times over with all types of clients in all types of industries!
Solution #1: Filtering Across Many Fields
Use Case:
You have data with multiple columns that might either be null or have a specific value that you don’t want. An easy way to dynamically get rid of those columns is to use this solution. If I knew that every single time there were 2 or 3 columns I would need to get rid of then I’d use the Select tool. Many times I don’t. I just know that for example if the column is all nulls, all blanks, all text when it should be numerical, etc then this works great. In my example I show you how once you’ve filtered to the values you want you then cross tab it back to the same table. However you could cross tab back to something different based on your needs. This is a great but flexible solution.
Steps:
- Input Data
- Filter to the subset if needed
- Transpose Data to have the Key Columns and Name, Values columns
- Filter on the Name or Values field
- Crosstab back to where you started or use a different field to group by
Solution #2: Using Data to Create a WHERE Clause or IN Statement
Use Case:
You have a long list of items that you want to filter your SQL query down to for better performance. It can be difficult and painful to have to type many values. Fortunately you can use this solution to speed that up. The added benefit is that you can use this to create a value list for an IN statement in the formula tool as well. A note of caution here is that sometimes this problem can be better or more easily solved by using a Join to do the filtering, just depends on a case by case basis.
Steps:
- Input Data
- Group By List Field (To get unique values)
- Summarize tool with Concatenate
- Place “Where” or IN” in Start box of Concatenate
NOTE: Make sure you look at your data and if there are values with a single quote that you use double quotes and vice versa 🙂
Solution #3: Get the Top X Number of Values
Use Case:
You want the top paid sales person per region or the top product by sales or anything of the sort. By using the combo of sorting and the sample tool you can get exactly that! In this example below I wanted the top selling product for each store on Saturdays.
Steps:
- Input Data
- Sort by field you want to sample
- Sample Top X values, Set group by to pick X number of values by each of the items in that group (ie Stores)
Solution #4: Quickly Creating Test or Sample Data
Use Case:
You want to know something can work (ie test) or you want to get started on a project or keep moving while data at the source might have a problem that is being addressed. Either way you don’t want to stop moving because of external situations. I use this all the time when I am creating examples for others or creating data sets for a macro I am building.
Steps:
- Input Data
- Filter to subset if needed
- Select data you want to use (you can do this by rows or values in a column)
- Right click, select “Copy Selected Cells with Headers"
- Right click on the blank canvas, select Paste
Solution #5: Filtering and Outer Joins
Use Case:
This is what we call a BOGO friends. If you want to know how to use a Join to filter records this works but also I always get questions about how does someone do something other than an inner join…this is how. Hopefully you can see how you would do a Left Outer Join as well as a Full Outer Join!
Steps:
- Input Data
- Item list to be used as filter
- Join on item (ie Product)
- Union with “J” first, then “L” or “R” depending on the join you want
There isn’t any rocket science here but I hope they help you with the day to day. I have attached the workflow here so you can walk through the configurations as well. If you use any of these already let me know! Also if you have any other design patterns you love using please share below.
All the best!
Joshua
