XML Web file - actual currency rate from National Bank

[Dejv]

Hi guys,

I have 3 excel tables and these specific columns:

*Employee: EmployeeID, OfficeID, Year Salary ...

*Office: OfficeID, Office Country, Office City...

*Hours: EmployeeID, Hours worked monthly

and separate connection *XML Web file: Currency, Rate        i.e.(AUD, 1.5983)

I would like to make an overview of the average salaries of employees in individual cities converted to chosen foreign currencies based on the currency rate.

I have successfully connected the XML Web file but how do I make an association to other tables in order to make required overview?

Do I need to create a new field with a calculation or Load Currency as a mapping table and use ApplyMap function? What is the easiest way to do it? 

Thank you in advance

[jheasley]

I do quite a bit of currency conversion, and the trick is to create an ID that can be tied to the conversion rate.  for my particular use case, the ID is FromCurr&'-'&ToCurr&'-'ConversionDate (USD-CAD-03/22/2018) and the rate.  I then use this as a mapping table into my app to normalize all currency into the desired rate.  By having one mapping table,  i have the bonus of being able to use the same applymap against multiple fields so that i can have multiple normalized rates.  This way if my app needs to have USD, EUR, and CNY, then i have the flexibility to do all 3 without extra effort.