Hacking Alteryx – Editing XML Directly

Ok, ok, maybe this isn’t technically hacking, but it sure feels like it!  While at the Alteryx Inspire conference this year I learned that you can actually access the XML behind any workflow tool effectively bypassing the GUI entirely.  Granted, you won’t need to do this very often, since they do have a good GUI, but there are cases where it sure comes in handy.  In one of my recent posts, I showed how this can be a big help with the select tool, and today I’ll show how I just used it on the formula tool.

First off, you have to edit your user settings to enable displaying of the XML properties of other tools.  Go to Tools > User Settings > Edit User Settings

Click on the Advanced tab and check off the box below:

Enabling this setting creates another tab on the properties window of any tool.  If you click on the new </> symbol icon, you can see the actual XML defining how the tool is configured.

Editing this XML directly can be far faster than using the GUI if you have a bunch of fields in your data stream.

Example Application:

We’ll look at an example of how you can quickly configure the formula tool to subtract one set of columns from another set.  Suppose your dataset has 40 columns.  The first twenty are labeled A1, A2, A3 …A20, followed by twenty more labeled B1, B2, B3 …B20.  You need to subtract every B column from every A column resulting in A1 – B1, A2 – B2, A3 – B3, etc.  Unfortunately Alteryx’s multi-field formula tool won’t work in this case since it doesn’t let you dynamically reference different column names.  This means you’re stuck with using the traditional Formula tool like this:

But who wants to sit there and type this out for all twenty columns?  Instead, we can click on the XML properties of the tool and note the XML format used.

We can then hop over to Excel and quickly build a concatenation formula to replicate the XML string for each function as follows.

Note that by referencing the field names listed out in separate columns, I can copy my formula down and it dynamically creates XML statements for each individual calculation.

Then it’s just a matter of copying those new XML strings, going back to Alteryx, hitting the Edit button in the XML properties window, and pasting in our edited XML.

Now if we click back to the normal tool configuration view, we see all our newly created formulas done! No more clicking or typing needed.  Now imagine how much time this would save you if you had 50 calculations to do!

You can imagine this process now for any tool in your workflow where you need to make a bulk change