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!
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.
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.
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 🙂
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.
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.
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!
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.