Dynamic Replace Tool

[AdamR_AYX]

Introduction

The Alteryx 6.1 release saw the addition of a new tool to the Laboratory section of the ToolBox: "Dynamic Replace".

This tool allows the conditional replacement of the contents of a data cell based on data from a data stream.

So to use the example which ships with Alteryx imagine you have a data set like this:

and want to band your data into 5 bands A to E based on the following definitions:

<20000 A

>20000 <40000 B

>40000 <60000 C

>60000 <80000 D

>80000 <100000 E

So how do you do that prior to Dynamic Replace? Well actually it's not that difficult. You add a record ID; transpose your data; do a single formula to create the bands; then finally crosstab the table back to the original structure.

That's not so difficult I hear you say. So why do we need the dynamic replace tool? Two reasons:

1. Speed! Imagine that table has millions of rows and over a hundred columns. The transpose tool will create potentially billions of rows all of which have to be run through that formula tool before having? to be crosstabed back. The dynamic replace tool allows the calculations to be done without having to change the data structure and will multi-thread for multiple columns.
2. It's dynamic (The clue was in the name). Imagine now that you want to calculate those bounds as part of your module. So we don't know what the cut off for band A will be until the module is running. You can't update that formula at runtime without going down a whole batch macro type of solution.

So how does it work?

So first thing you need is the data which defines the rules for your replacements. In our example this looks like this

This can be generated automatically as shown in the example module and is what let's those bands be dynamic. Then your data goes into the left input of the dynamic input tool and the above table goes into the right input. The tool is configured like so:

• The Field Name Field is the field in our above table which contains the name of the field we will be doing the replacement on.
• The Boolean Expression Field is the test we will do on a particular field to decide if we will replace it's value. The special value _CurrentField_ represents the value of the cell to be replaced. These are evaluated in the order they are passed into the tool (so if you pass them in the correct order then you don't need to define the upper bounds for the ranges).
• The Output Value Field is the value which is inserted into the cell when it finds a true expression test. So in our example the bands A through E.

There are also options around the field types of the output field which are failry self explanatory.

The tool has two outputs:

• O - Which is the input data with the replacements made.
• S - Which contains a summary report detailing what replacements? were made. Very useful for checking the tool worked as expected.

 

But what about that checkbox?

Ah yes the "Values are Expressions/Formulas" checkbox. Now things begin to get rather interesting. With that box checked the replacement values get evaluated as Alteryx expressions before the current value is replaced.

So if anyone remembers my Dynamic Formula macro this problem can now be solved with the Dynamic replace tool!

So instead of having say a value A through E in our Output Value column we can have an Alteryx expression. In this example:

"MOSCYAPA01+MOSCYAPA02+MOSCYAPA03+MOSCYAPA04+MOSCYAPA05+MOSCYAPA06+MOSCYAPA07+MOSCYAPB01+MOSCYAPB02+MOSCYAPB03+MOSCYAPB04+MOSCYAPB05+MOSCYAPB06+MOSCYAPC01+MOSCYAPC02+MOSCYAPC03+MOSCYAPC04+MOSCYAPC05+MOSCYAPD01+MOSCYAPD02+MOSCYAPD03+MOSCYAPD04+MOSCYAPE01+MOSCYAPE02+MOSCYAPE03+MOSCYAPE04+MOSCYAPE05+MOSCYAPE06+MOSCYAPF01+MOSCYAPF02+MOSCYAPF03+MOSCYAPF04+MOSCYAPF05+MOSCYAPG01+MOSCYAPG02+MOSCYAPG03+MOSCYAPG04+MOSCYAPH01+MOSCYAPH02+MOSCYAPH03+MOSCYAPH04+MOSCYAPI01+MOSCYAPI02+MOSCYAPI03+MOSCYAPI04+MOSCYAPI05+MOSCYAPJ01+MOSCYAPJ02+MOSCYAPJ03+MOSCYAPJ04+MOSCYAPJ05+MOSCYAPK01+MOSCYAPK02+MOSCYAPK03+MOSCYAPK04+MOSCYAPK05+MOSCYAPK06+MOSCYAPL01+MOSCYAPL02+MOSCYAPL03+MOSCYAPU00"

which we have dynamically generated. Check out my example here.

This makes the Dynamic Replace a powerful tool for the advanced Alteryx user's ToolBox!