Sum raw data up using mapping file

[ssophiey]

Hi all,

I am really new to Alteryx and the community and would like some ideas around the functions that I am trying to achieve.

My raw data set A is like below:

Account No.	Balance
10001	100.00
10002	100.00
30001	100.00
30002	100.00
41000	100.00
42000	100.00

And my mapping file A from the raw data is like this: 

Cash	10001
	10002
Property	30001
	30002
Revenue	41000
	42000

And the final result i want using the mapping file is like this: 

Cash	200.00
Property	200.00
Revenue	200.00

Is there any function available for the result that I want to get as described?

After getting this table, I also have a another mapping file B looks like this:

Cash	85000
	85001
Property	95000
	95001
Revenue	75000
	75001

With another set of raw data setting up as below:

Account #	85000	85001	95000	95001	75000	75001
Balance	50.00	150.00	60.00	140.00	70.00	130.00

And the final final result I want to achieve from the mapping file is like this

Account	Per Map A	Per Map B	Difference
Cash	200.00	200	= B - A
Property	200.00	200	= B - A
Revenue	200.00	200	= B - A

Any idea is welcomed and thank you all in advance for your help!

[JosephSerpis · Accepted solution]

Hi @ssophiey I mocked up a workflow that produces your output. Let me know what you think?

[ssophiey]

Hi @JS420, 

Thanks for the prompt reply and your very clear and easy-follow workflow!

This is exactly what I want to do.

Thanks again for your help!