Friday 30 August 2019

Dynamic Formula: A performance use case

In the Alteryx Community a post caught my attention.  The member wanted to perform calculations based upon the previous field (column).  For the purpose of this post, I've modified the use case as follows:

Within my Alteryx workflow I need to calculate the difference between each numeric column of data (e.g. monthly data) and create "delta" columns that calculate that difference.  Success of the solution depends upon both not needing to update the workflow (e.g. dynamic calculations) and the performance of the solution (not needing to transpose the data).
My friends Thales & Esther (EstherB47recommended transposing the data.

When dealing with this kind of situation, the best you could do is to transpose your columns into rows.
This way you can work with Multi-Row Formula, that can be much more flexible in regards to what you're looking for.

So basically use Transpose Tool and you'll have a better situation to compare previous records against the current ones.
Not me!  I know how to avoid the transpose tool and how to dynamically calculate formulas.  Try this yourself and don't use a Transpose Tool or Multi-Field Formula.  Imagine hundreds of columns with thousand or more rows.  Here's some simple input data:

This is the desired output:

The solution looks like this:

Step 1:  Use a SELECT tool to limit your input to just the "KEY" field(s) plus the numeric fields used in the calculation.  I assume that the key is STRING.
Step 2:  Use a FIELD INFO tool to get the metadata (names & types) of incoming data.
Step 3:  Use a FILTER tool to remove any non-numeric fields.  If the KEY is numeric, we're going to have a problem.  You'd need to use a SAMPLE tool to skip N records (N is the number of key fields).
Step 4:  Configure a FORMULA tool to create "Output Field" names for each of the new fields.
Step 5:  Configure a MULTI-ROW Formula tool to create an expression of:
"[" + ToString([Name]) + "] - [" + ToString([Row-1:Name]) + "]"
Step 6:  Use a sample tool to remove the calculation of Delta from the first record.
Step 7:  Configure the CReW Dynamic Formula tool.

The workflow has been saved within the Community post  (above) and a link is available to the yxmd file (here).

This might make for a clever CReW macro addition.  Is there a volunteer to prototype a generic macro for the community?