Append columns to the DataSet by sum the logic from corresponding columns

[187]

Hi Alteryx engineer, i have data set like below 

key	USD_DIV_1	USD_DIV_2	ROC_D
APAC	1.000000	2.000000	3
EURO	4	5	22

AND I have lookup table like below

KEY	LOOKUP
DIV RATE	DIV
ROC RATE	ROC

I want to get below results, which means I read data from lookup table and append the column to the dataset and it is value set by the rules which is get lookup value from lookup table for example DIV RATE is DIV then I sum the value from original dataset for those columns contains "DIV" , and do the same logic for ROC RATE

key	USD_DIV_1	USD_DIV_2	ROC_D	DIV RATE	ROC RATE
APAC	1.000000	2.000000	3	3	3
EURO	4	5	22	9	22

[FinnCharlton]

Hi @187 , I think you're looking for something like this? This should work for any number of different columns and lookups, you would only need to tweak the regex logic:

[ShankerV]

Hi @187 

Please find the expected output.

The workflow will work dynamically for any inputs and if there are more Key APAC EURO, America, Australia are added.

Many thanks

Shanker V

[BS_THE_ANALYST · Accepted solution]

@187 nice problem! I think a mixture of @ShankerV's and @FinnCharlton's logic can cut down the tools. I learned alot from these two on this one 😄

[ShankerV]

Hi @BS_THE_ANALYST 

Yes, you are right. The mixture of 2 solutions along with your idea too.

Nice one!!!!

[187]

Thanks guys, your solution are awesome, that really solve my problems thanks!