Dynamic Field update using mapping file

[hanz_e]

Hello Everyone,

I have a data which looks like this

Name	Age	Country	Pay1	Pay2	Pay3	Pay4	Pay5	Pay6	Pay7
Jacob	23	UK	4367.47	34685.26	3598.76	1234.58	9845.67	4756.87	54635.67
William	31	USA	87458.67	7647.76	356276.87	73846.56	8728.76	87348.78	87248.78
Kumar	27	India	7246.64		93748.87	23674.87	45984.87	83748.77	23763.8
Mazood	24	UAE	6728.78	25753.98	34875.98		87348.98	23672.98	236724.9

I want calculate a new set of fields using a mapping sheet

Standard Name	Client Data
Candidate	Name
Age	Age
Nation	Country
Salary1	Pay1+Pay4+Pay5
Salary2	Pay2+Pay6
Salary3	Pay3
Salary4	Pay7

I am trying to create an Alteryx app which will calculate the Salary fields from the mapping sheet and the combination of Pay fields might change from case to case, and then I have to use these Salary fields for further calculation.

So could you please help me to create a workflow which reads the combination from the mapping sheet and then dynamically calculate the Salary fields?

Thanks in Advance!

[danilang · Accepted solution]

Hi @hanz_e 

This should get you started

Split your mapping table into 2.  The first one has the field rename info.  Just add new records to this to map new client fields to the standard ones.  This mapping table is used by a Dynamic Rename tool to standardize the field names.

The second table contains formulas.  These need to be in the same format as if you entered them in a formula tool, i.e. [Pay1]+[Pay4]+[Pay5].  These are used in the Dynamic Replace tool which is set up to act in formula mode.  It evaluates each of the formulas using the data in each of the rows to give you the results

Dan

[Qiu · Accepted solution]

@hanz_e 
If it is pure addition, we can also do this.

[vizAlter]

@danilang - Awesome method! 👍

[atcodedog05]

I can agree with you on that @vizAlter.

@danilang - Awesome method! 🙂👍

Always something new to learn

---

[shreyanshrathod]

Hi, 

I see that your query has been resolved, but still wanted to attempt it because it's an interesting one.

 @danilang , I'm a fan of yours. Always something to learn from you!!!

Regards,

Shreyansh Rathod

[Qiu]

@danilang @hanz_e 

Maybe you can consider to submit this as a weekly challenge idea?

Myself still not get better with the Dynamic Replace tool. 😁

[atcodedog05]

Hi @Qiu 

As i remember there was a weekly challenge which had usage of dynamic replace but it had been a really long while🤔