Using a formula stored in a field to calculate the result

[DRA2353]

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

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

[danilang · Accepted solution]

Hi @DRA2353 

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

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 

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

[DRA2353]

Wow. Amazing! Thank you so much Dan.

[sonseeahray]

@danilang 

Hi Dan,

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.

Base table

Lookup table

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

=309,619.139

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?