This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
The Dynamic Replace tool is what you're looking for. It has an option that allows you to specify that the replacement value are expressions and will evaluate them. The only thing to watch for is the the tool will apply all the formulas to all the records in the data, with the end result being last formula applied to all the rows. If you have different formulas on each row, you'll have to wrap the tool in a batch macro that processes the data one row at a time.
You'll also have to use valid Alteryx syntax. In your case the formula would have to be [Numerator]/[Denominator] with all field names surrounded by 
Here's an example of the main program and the Macro wrapper
The main program just sets up the data so that it can be processed one record at a time by the batch macro. Add a recordId field and pass this to the control parameter of the macro. Pass the main data to the data input.
The macro is where the magic happens. The first part, up to the filter, is what processes the records one at a time. Every time the macro loops, the Update Value tool changes the formula in the Filter tool to select only one record. On loop one, its Record 1, loop 2, Record 2, etc. The Create Result formula adds in a field to store the value(Result) and initializes it with 0. The is sent into the D(ata) input of the Dynamic Replace. The bottom branch builds the 3 columns that are needed for the replacement operation to work. The Select selects only the Formula column, then the next Formula tool adds a int32 field called Boolean field and sets it to -1(True). It also adds a field called FieldName which stores the name of the Result field that was created earlier. This is passed to R(eplace) input of the Dynamic replace. Note that the Dynamic Replace tool doesn't actually accept Boolean fields as the Boolean field. You have to use an integer field and initialize it to -1
The Dynamic Replace is configured to use the 3 fields form the Replace input and also to Evaluate the values in the Formula field as formulas.
The result are as follows
The attached alteryx package contains both the main program and the DynFormula macro. You should be able to use this as template to build you own.
Your macro has enabled me to get a single calculated field for a combination. Is there a way to modify this so that I can run multiple formulas through it?
Below is the content to a question I posted earlier today:
I am attempting to find a solution to perform calculations based on 2 tables. It's difficult for me to explain, so I have screenshots to show what I am trying to accomplish. The final base table will contain 1,000s of combinations to perform calculations from.
The field I called "Add values" contains the values that need to be added together from the values inthe lookup table. The screenshot is a very small sample. The lookup table will contain about 100 lookup columns.
So, for the V-A combo 20069-130115, the "Add values" field would be the sum of the lookup values for those fields, which would result in this calculation
I am able to get a single calculation using the Dynamic Formula macro, as in the screenshot below.
How would I modify the Dynamic Formula Macro accomplish getting multiple calculations for one V-A?