A global customer has data for multiple currencies in a Transaction table. The customer needs to view the global figures in USD, GBP, and EUR using the currency exchange rate for the specific date of the transaction.
The currency exchange rates are stored in a separate table that has four fields: FromCurrencyCode, ToCurrencyCode, EffectiveStartDate, and ExchangeRate. It does not hold exchange rates for every day, but only when a new exchange rate becomes effective.
Which scripting technique should a developer use to ensure the figures are available in the required currencies?
load the ExchangeRate table as a Mapping table and then use Applymap to retrieve the correct ExchangeRate when loading the Transaction table
load the ExchangeRate table and then use Peek to store the ExchangeRates in variables to be used for conversion when loading the Transaction table
load the ExchangeRate table using the Previous function to determine the EffectiveEndDate and then use the IntervalMatch function to retrieve the correct ExchangeRate when loading the Transaction table
load the ExchangeRate table and then use the Lookup function when loading the Transaction table to retrieve the correct ExchangeRate. If no ExchangeRate is found for the Transaction date, use the Previous function within the Lookup function to retrieve the earlier effective ExchangeRate
IntervalMatch is the only way to link tables where there may not be an exact match between values in the tables. The IntervalMatch function will link based on a range of values, called an interval, which is created by using Previous(EffectiveStartDate) and EffectiveStartDate. The Previous(EffectiveStartDate) is the EffectiveEndDate.
ApplyMap and Lookup require an exact match. The Peek function returns the contents of a fieldname in a specific row of a table, but storing the ExchangeRates in variables will not achieve the desired result.
