Friday, 15 November 2013

Dynamic Formula Macro


The Dynamic Formula Tool allows you to dynamically create formulas using a data feed. Effectively allowing you to write formulas by using other formulas, the summarize tool or other techniques. Great for when you need to write a long or complex formula or create a lot of formulas and don't want to do it manually.


What Does it Do?

Ever wanted to generate formulas using formulas?  Then the Dynamic Formula macro might be just what you are looking for.

How Does it Work?

First released on this blog back in May 2010; that blog post provides some more background to the tool.  This release sees the module upgraded to horizontal layout and makes use of some of the new Alteryx features that have been added since it was first written:


This new method is ideal for when you are building your own XML as it correctly escapes the XML meta characters.Which are " ' < > &.

So now the tool can dynamically build formulas with greater than or less than comparisons.

Output fields change based on the data input into the macro

This is a new check-box on the macro properties window and the dynamic formula macro is a perfect example of what it is for.  So at configuration time we don't know what fields will come out of the macro because the field names of the new fields are determined by the data that is input into the bottom input.  Without this check-box checked the macro forgets what fields it is outputting whenever the module config is refreshed.  With it checked then after the first run it remembers its output fields, which is exactly what you want.

Dynamic Replace Tool

It is also worth mentioning that since I originally wrote this macro the dynamic replace tool has appeared in the Tool Palette, which with a bit of manipulation I believe can be made to perform the same functionality as this macro.  However I still like the Dynamic Formula for its simplicity.