XML Web file - actual currency rate from National Bank
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?
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.
