Engine Works

FreeRangeDingo · ‎05-23-2022

I’d like to share three tools that start and end every Alteryx workflow I build to drive consistency and optimization.

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.

Starting Workflows

Select

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.

via GIPHY

Furthermore, I've learned that while reordering columns is time-consuming, it pays off in time saved down the road. This is especially true for large workflows with a lot of columns. Hunting through long lists of columns is a time suck.

Multi-Field Formula

Next, I use the Multi-Field Formula tool to remove 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 skip that tool because it lacks the checkbox for “Dynamic or Unknown Fields.” The Multi-Field Formula tool contains this option, 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 easily with a drop-down rather than picking and clicking.

Auto Field

Lastly, I add an Auto Field tool to make all 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 gigabyte off one of my data sets.

Warning! The Auto Field tool can change the data type of your columns. Take care with long numbers that are key columns or identifiers. I usually set these as strings, but Auto Field will change them to integers. Auto Field will also change any column with all nulls to Boolean. Check out this post to see how that might be helpful.

Ending Workflows

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 output. 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 through 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.

Conclusion

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!