Alteryx designer Discussions

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

Method to apply calculations to 1,000s of combinations using Dynamic Formula macro

Highlighted
8 - Asteroid

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?

 
 

This is a sample of what a list of calculations for one V-A combination would be:

sonseeahray_0-1581971333701.png

 

 

Highlighted
13 - Pulsar

Hi @sonseeahray,

 

I might be missing something in your data, but you should be able to accomplish this without a macro.  By pivoting the data from each table into a similar format, a Join tool will do the work for you then a Summarize tool to get the results.  The final Join tool attaches the results to the Base Table matching on Field.  Depending on your data formats (you show two) there may be additional data cleansing to get the formats to match.

 

Lookup Tables results.png

 

Labels