Total amount using the saved expression

[naveen_kr]

I have two excel files. I have done a left join in Alteryx for these two files using Dealer group no. and Client code. I want create a flow in Alteryx where if NAT is Selected it will calculate the total amount using expression saved in the second excel file's "Amount" field. for example for Dealer group no. 10000 the total amount will be [A_Tax+B_Tax+C_Tax+D_Tax] i.e. 1400 and for Dealer group no. 10100 the total amount will be [A_Tax+B_Tax+C_Tax] i.e. 900. and so on. I want to use a formula tool and add a new column "Total Amount" where it uses the expression saved in the second excel files to add the Total Amount. Using a batch macro is also fine. Please help me with this.

Client Code	Dealer Group no.	A_Tax	B_Tax	C_Tax	D_Tax
NAT	10000	200	300	400	500
NAT	10100	200	300	400
EFR	20000	200	300	400	500
EFR	20100	300	400	600	700

Client Code	Dealer Group no.	ACIC	Amount
NAT	10000	No	A_Tax+B_Tax+C_Tax+D_Tax
NAT	10100	Yes	A_Tax+B_Tax+C_Tax
EFR	20000	No	A_Tax+B_Tax+C_Tax+D_Tax
EFR	20100	No	A_Tax+B_Tax+C_Tax+D_Tax

[Deano478]

@naveen_kr if a user is going to have to select the Client Code then you will need to go down the route of an analytic app using the interface tools for user input if you have no looked at analytic apps before here is a starting point.

Creating Analytic Apps 

[kylecoleman]

Rather than subbing in the contents of the "Amount" cell as a formula expression, you could use it to inclusively add the tax values if the code is present in the string.

ToNumber(
IF Contains([Amount],"A_Tax")
THEN [A_Tax] ELSE 0 ENDIF +
IF Contains([Amount],"B_Tax")
THEN [B_Tax] ELSE 0 ENDIF +
IF Contains([Amount],"C_Tax")
THEN [C_Tax] ELSE 0 ENDIF +
IF Contains([Amount],"D_Tax")
THEN [D_Tax] ELSE 0 ENDIF
)

[naveen_kr]

this will not help as i have multiple client codes and multiple conditions. There should be some kind of tool where it will pass the saved expression to the formula tool.

[naveen_kr]

I am using the analytical app for selecting the client code. I need the solution for the above problem. I have multiple client codes and multiple conditions. We must pass the saved expression in the Amount field to a formula tool so that it can calculate the total amount.

[kylecoleman]

If you were already planning on joining the two tables and using a cell to hold the formula for each client scenario, the formula I suggested would work without any extra effort.

[OllieClarke · Accepted solution]

Hi @naveen_kr 

not 100% sure I follow you, but the dynamic replace tool might be what you're after here:

This lets you update 1 (or more fields) based off written out formulae without needing to use batch macros

There's more info here: https://help.alteryx.com/current/en/designer/tools/developer/dynamic-replace-tool.html

Hope that helps,

Ollie

[abacon · Accepted solution]

I believe I was able to do this without a batch macro. I haven't added analytic app functionality but it sounds like that part you have figured out.