Dynamic Replace Tool | Apply Dynamic Replace Tool on Multiple Column

[luqmantmf]

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.

[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

[luqmantmf]

Thank you @Yoshiro_Fujimori 

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

[Yoshiro_Fujimori]

@luqmantmf ,

If the user gives you the formula and the column to apply in a table, 

if the formulas applied to all the rows,

you may be able to make it to a Batch Macro. (never tried, just an idea)

[luqmantmf]

Ok,

I'll try.

Thanks again @Yoshiro_Fujimori . 👍

[PhilipMannering · Accepted solution]

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.

[luqmantmf]

Hi @PhilipMannering,

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.