Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-tos.
Alteryx Partner

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.

 

solution1.png

Steps:

 

  1. Input Data
  2. Filter to the subset if needed
  3. Transpose Data to have the Key Columns and Name, Values columns
  4. Filter on the Name or Values field
  5. 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.

 

solution2.png

Steps:

 

  1. Input Data
  2. Group By List Field (To get unique values)
  3. Summarize tool with Concatenate
  4. 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.

 

solution3.png

Steps:

 

  1. Input Data
  2. Sort by field you want to sample
  3. 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.

 

solution4.png

Steps:

 

  1. Input Data
  2. Filter to subset if needed
  3. Select data you want to use (you can do this by rows or values in a  column)
  4. Right click, select “Copy Selected Cells with Headers"
  5. 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!

 

solution5.jpg

Steps:

 

  1. Input Data
  2. Item list to be used as filter
  3. Join on item (ie Product)
  4. 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

Comments
7 - Meteor

Love it! short and sweet. All useful, I found all useful. I can tell you that I will apply #1 and #2 today!!! 🙂

7 - Meteor

One of the things I have done is set up a basic workflow named tools library with tools I use frequently. For example, the DB2 system I work with has at least 6 different possible formats for date. So I set up formula tool with all of those date formats translated so I can use it for an input tool. I also set up a couple of multi-field formula tools to translate those dates to Alteryx formats for working with them in various workflows.

 

One of the best things about Alteryx is its modularity!

Alteryx Partner

Love it @PCAM - That's very cool! One thing that might make it easier is if you created macros of each of those items you need then you can also create your own tool pallete and have all those macros at the ready whenever you need instead of having to open a file 🙂 Just a suggestion. Keep it up!

8 - Asteroid

@PCAM, I will definitely be creating a workflow = Tools Library, great idea! 

 

@joshuaburkhow - Great post, very helpful! I am interested to get your thoughts related to your last comment. When would you recommend using a macro vs bring in tools into a new workflow?

- My experience: macro = quite slow, configure new tools = little more work upfront but quicker.

I am with internal audit, so my workflows (e.g. Travel and Incidentals) tend to never be completely finished, so macros upfront have been rough to wait on with as I continue to add more analytics. I do use containers.  

 

New Alteryx User

 

 

Alteryx Community Team
Alteryx Community Team

@PCAM you can also save an expression in the Formula tool with the save.png button for later re-use

8 - Asteroid

LOVE this insight. As a new user of Alteryx, the following advice/framework shapes how I will view my work from this day forward.  (MIND BLOWN.)

 

"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".

 

Thanks for putting this resource together.  I truly appreciate it. 🙂

7 - Meteor
Love it, It was indeed helpful
Alteryx Partner

What a great article. Thanks Joshua!

Alteryx Partner

Thanks @MikeLR !  Happy you found it useful!

Alteryx Partner

Thanks @sanjay_shinde !

12 - Quasar

Small syntax issue....missing text under Solution #4, step 5

Labels