Create computed field with multiple IF's from separate table

[munluva99]

Hello,

I am brand new to Alteryx and am trying to create a new field in the primary table [Table 1], which has all other information.  [Table 1] has all relevant information I need but has many different currencies. I need to create a formula so that I have one column with all the same currencies.  My currency [CUR] information sits in another data table [Table 2] and lists all the currencies and the exchange rate [XRATE] for each month.  

How do I create a field in [Table 1] that will look up the currency [CUR] AND period [PER] (i.e. month) from [Table 1] in [Table 2] to return two values from [Table 2] if the prior two conditions are met? The two values to be returned would help me to later create a formula to calculate the transaction amount in all USD.

Another note which makes this more complicated is that [Table 2] has a field which indicates whether I will need to either multiply or divide the [XRATE] to get the currency to USD. In a best case scenario, it would read the period/month and currency and calculate the transactional amount into USD in one action. 

Perhaps this is too complicated for a beginner but hoping Alteryx can provide quicker solutions for me.

[cmcclellan · Accepted solution]

This is how I would do it :)

[munluva99]

Amazing! Thank you so much. I don't think I could have come up with this now (as a new user). 

[cmcclellan]

It's just walking through the problem slowly, but I've done this sort of currency conversion a few times in different tools so I knew what to do quickly.

The first time is always the hardest, because you have to learn so much at the same time.

[munluva99]

thanks so much for responding to this question. hoping that i get through this "new" phase and learn quickly. cannot thank you enough!!