Calculate New Column From Scanning Individual Column Values

[arshadsaad]

Hello! 

I want to calculate a value in Total Cost column by scanning the formula in the formula column. 

For example: 

Formula column value: 

Unit Rate * (Distance_Kilometers - 20) / 100 * POW( MTOW_Metric Ton / 50, 0.5) 

The Total Cost should be calculated by taking the value from the Unit Rate column and multiplying it with the value in the distance column and so on. 

Essentially I want to scan individual sections of the Total Cost column value and use it to multiply/divide/add/subtract values in other columns. 

One thing the formula needs to take care of this is: 

It should only calculate values for when MTOW of a particular falls between the values in the MIN and MAX column. Otherwise it should be zero. 

I would really appreciate any help

[JamesCharnley]

Hi @arshadsaad,

The way to call a formula like that is to use the Dynamic Replace tool. The issue with your workflow is that the formula you're calling needs to be fully bracketed correctly, formatted exactly as your fields are within the workflow, so rather than me adding those brackets manually, it'll be best to do that wherever the formula is being pulled from so the workflow is still dynamic moving forward. 

I've attached an example of how the dynamic replace tool works, and more documentation can be found here: https://help.alteryx.com/20221/designer/dynamic-replace-tool#:~:text=Use%20Dynamic%20Replace%20to%20....

I think as for the point about being between the MIN and MAX, that can just be changed in a formula after the dynamic replace.

[binuacs]

@arshadsaad One way of doing this. I added new field expression and updated the formula. 

[arshadsaad]

Hey binuacs, 

Could you please share the workflow you used for this? I'm new to Alteryx and I think that will help :)

[arshadsaad]

Hi @JamesCharnley, 

Could you please help me understand the logic behind the two Formulas? 

Thank you!