This week, I’m going to share the three tools that start and end every Alteryx workflow I build in order to drive consistency and functionality.
Every Alteryx workflow I build starts and ends with the same tools. This gives me something solid to start from and end with. Not only do I know that key tasks are addressed right out of the gate, but other developers who touch my work also know what to expect. And we all work in teams, so that’s worth something. Those three tools are Select, Multi-Field Formula, and Auto Field.
After every Input tool, I add a Select tool to rename and reorder columns. Renaming columns in a Select tool right after the input has two advantages. First, renaming in Select involves fewer mouse clicks than renaming in an Input tool. If you rename columns more than once, this will become readily apparent. Second, if you consistently rename in the Select tool after the Input, you don’t have to hunt to find where you renamed a particular column. The renaming will always be in the same place.
I also recommend picking and sticking with a column naming convention from the very beginning of your work. There’s nothing worse than realizing you should have started with a naming convention and then having to do rework to implement one. It’s so easy to get wrapped up in the building and developing that you don’t pay attention to naming. I mean…it’s boring, but it’s very important.
Furthermore, I’ve learned that while reordering columns is time-consuming, it pays off in time efficiency down the road. This is especially true if you have large workflows with a lot of columns. Hunting thru long lists of columns is a time suck.
Next, I use the Multi-Field Formula tool to trim away whitespace. This data wrangling task is particularly important because whitespace can throw off joins deeper into your workflows. Now, I know many of you use the Data Cleansing tool for this task, but I hate that tool because it doesn’t all for new columns. The Multi-Field Formula tool contains a checkbox for “Dynamic or Unknown Fields”, which means new or renamed columns will also be trimmed. The Multi-Field Formula tool allows me to set it and forget it. With the Data Cleansing tool, you have to keep revisiting it to make sure no columns were missed, which is also a time suck. And, you can also filter down to text columns very easily rather than picking and clicking.
Lastly, I add an Auto Field tool to make all of my columns as small as possible. Why do this? Well, Alteryx data sets can get huge, and scraping a few unnecessary or extra spaces off the end of each column can make a big difference. Check out this post I wrote last year showing how the Auto Field tool took half a gig off of one of my data sets.
When ending workflows, I use the exact same tools in a slightly different order.
I trim all whitespace again with the Multi-Field Formula tool. Why do this again? It’s possible that calculated columns contain whitespace. It seems far-fetched, but I’ve seen it happen.
Then, I use the Auto Field formula tool to make everything as small as possible prior to writing the output.
The workflow completes with the Select tool not only to rename and reorder before the Output tool but also to make sure the Auto Field tool didn’t do something wonky with my columns. I have come to expect that Alteryx will take certain string columns and turn them into integers. I know I will need to change them back before writing the ouput. But, didn’t I say that I should always rename columns at the start? Experience has taught me that sometimes a workflow is just too big to go back thru the whole thing again, and the easiest thing to do is rename a column at the end. And reordering columns at the end allows you to rearrange columns added with calculations or additional joins.
There you have it. Those are the 3 tools that I use at the start and end of every Alteryx workflow to ensure consistency and functionality. If you have any other tools you use in a similar fashion, I would love to hear about them. Thanks and have a great week!
Pingback: How to Fix Query Timeouts in Alteryx » The Analytics Corner
Pingback: Remove Leading Zeros in Alteryx » The Analytics Corner
Pingback: Update SharePoint Drop Down Lists Using Alteryx Tools » The Analytics Corner