Using a formula stored in a field to calculate the result

Hi All,

Just wondering how to do a simple calculation as shown below

NUMERATOR DENOMINATOR FORMULA SCORE
5 10 NUMERATOR/DENOMINATOR 0.5

I want to calculate the SCORE based on the formula stored in the FORMULA field. The FORMULA field stores the names of the columns used in the calculation

Expression

Accepted answers

danilang

Hi @DRA2353

Here's an example of the main program and the Macro wrapper

Main Main

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.

Macro Macro

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.

Dan

Main.yxzp

All comments

danilang

Hi @DRA2353

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 []

Dan

DRA2353

Thanks Dan. I'm new to Alteryx and I have not used either dynamic replace tool or batch macros but I will give it a try.

DRA2353

Can the Dynamic Replace tool accept data from multiple columns and create a new result column?

What I'm seeing is it can read only from one column and replace an existing column.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703