Dynamic Replace Tool | Apply Dynamic Replace Tool on Multiple Column

Hi Community,

I need help on below situation.

Data Input:

Expected Result:

I can use Dynamic Replace Tool if there is only 1 column that contain formula, for example formula for TOTAL column only or formula for TAX column only.

Please advise how to provide solution for the problem above, because number of columns that contain formula, can be changed dynamically by user.

Thank you very much.

Change Formula in Multiple Column.xlsx

Developer

Developer Tools

Accepted answers

philipmannering

Hey @luqmantmf

I've tried putting this in an iterative macro so that it only executes formulas that it can in each iteration (i.e. no dependents). Needs testing, but seems to work for your sample data. See attached.

Dynamic Formula.yxzp

All comments

Yoshiro_Fujimori

Hi @luqmantmf ,

This may not be a solution for you, but I would share what I tried.

Dynamic Replace tool has two Input anchors, D for Data and R for Replacement.

The formulas to replace should be input from R anchor and original data should be input from D anchor.

You can specify multiple fields in Replacement data set, but Dynamic Replace tool seems to calculate based on the initial value from D anchor.

So if you want to calculate TAX after TOTAL is calculated, you would need to do it separately as below workflow.

However, if the number of columns that contain formula can be changed, I do not know how to deal with it.

If the formula is set by user outside of Alteryx (say Excel), it may be easier to process it before input to Alteryx.

Workflow

Input to D anchor

Input to R anchor (1st tool)

Input to R anchor (2nd tool)

Output

DynamicReplaceMultipleColumns.yxmd

luqmantmf

Thank you @Yoshiro_Fujimori

The problem is, the user only submit the formula, while the calculation is process by Alteryx.

Hey @luqmantmf

I've try using different data as below

and, the workflow that you've build, can calculate TOTAL, TAX, TAX 2, and TOTAL 2 column.

Thank you very much. 👍

philipmannering

Nice! Thanks for showing - pretty pleased with that.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703