Friday 14 May 2010

Dynamic Formula

A recent post on the Alteryx forum from Stephs (here) got me thinking: wouldn't it be useful if there was a Dynamic formula tool?  I think this would solve Stephs problem in that they could build formulas (with the formula tool) to compare the fields and use an if statement to choose between the file and database version and then push the data back to the database (that's if I've understood the problem correctly, if not I've already thought of some other great uses for this tool.).

Well the motto of any advanced user of Alteryx should be "If you want a new tool in Alteryx, build it!" So here it is:

Just drop all the files in the zip into your macros folder. Usually something like C:\Program Files\SRC\Alteryx5.0\RuntimeData\Macros\.

The two interesting features of this macro are :
  • It's the first time I have made use of the macro action "Update Raw XML", which worked very nicely.
  • I have used the batch macro tool in a way it wasn't particularly designed for.  I used it to run a single iteration which you might think defeats the point of the 'batch' feature, but it allows you to take data from the data stream and insert into the properties of the formula tool.  Using this technique, any Alteryx tool could be made to have dynamic properties!  I'm sure there must be some more uses out there for this technique.
Now for one of those other uses I've thought of.  Remember this query from the Alteryx Team blog here?

Hi Tara,
I have a table with 60+ fields. I need to create an extra field that is the total of all the 60+ fields. So far, I've been doing this with the formula tool and writing an expression as follows "[01]+[02]+[03]+[04]+[05]+[06]+[07]+[08]+[09]+[10]+[11]+[12]+[13]+[14]+[15]….. etc". Just wondering if there is a quicker way to do this? If not, can you add as a suggestion for a future release?

Well with the dynamic formula tool you can write this formula dynamically!  Plus it becomes very easy to also create subtotals based of the field names.  Check out the below module showing how this could work:

If anyone else thinks of some other uses for this tool then add a comment here or drop me a note.


  1. Nice post Adam. It is true that batch macros are more about dynamic configuration than they are about batch processing. One of our 1st uses in house was to make the Allocate input tool dynamic (for Alteryx web). The name "batch macro" is misleading. If anyone has a better name, I am all ears.

  2. Ah, I see. And there was me thinking I'd come up with a clever new use... :-) I like "batch macro" as a name though, it makes sense to me.