Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
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!
SOLVED

Using a formula stored in a field to calculate the result

Highlighted
6 - Meteoroid

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

Highlighted
17 - Castor
17 - Castor

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 

Highlighted
6 - Meteoroid

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.

Highlighted
6 - Meteoroid

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.

Highlighted
17 - Castor
17 - Castor

Hi @DRA2353 

 

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

 

MainMain

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.

 

MacroMacro 

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

 

Dyn.png

 

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

 

Results.png

 

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

 

 

 

 

Highlighted
6 - Meteoroid

Wow. Amazing! Thank you so much Dan.

Highlighted
8 - Asteroid

@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

sonseeahray_0-1581959310142.png

 

 

Lookup table

sonseeahray_1-1581965095174.png

 

 

 

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

sonseeahray_0-1581966646530.png

=309,619.139

 

I am able to get a single calculation using the Dynamic Formula macro, as in the screenshot below. 

sonseeahray_0-1581968162828.pngsonseeahray_1-1581968205009.png

How would I modify the Dynamic Formula Macro accomplish getting multiple calculations for one V-A?

 

sonseeahray_0-1581968595742.png

 

 

 

Labels