Currency conversion by matching date

[sifatnabil]

Hi all,

I have a table of exchange rates that updates every 1st of the month:

I want to convert the revenue figures in a revenue table using the rates in the column XRATE above.

I tried solving this by using a mapping load -

mapAUAPfxrates:

MAPPING LOAD

AUAPfxdate,

XRATE

Resident AUAPfxrates;

And then doing ApplyMap in the revenue table:

REVENUE_3/ApplyMap('mapAUAPfxrates', DATE_1)

But this doesn't work (gives strange numbers). Also, the DATE_1 refers to the date of the revenue, and this varies widely from the 1st to the 30th of any month. However, the exchange rates in XRATES are only listed by month, e.g. Apr-2014, Feb-2014.

How can I match the date in the exchange rates table to the date in the revenue table, to get the correct exchange rate?

[#2 · Accepted solution]

Hi

What you need to do in the revenue table is create a new date field that is the same format (you may need to use MonthEnd or MonthStart) as AUAPfxdate. Then you can either just use the Syn Key to join the 2 tables (or create a key), or join the ExRate table to the Revenue table.

I can't see what the exact date format of the AUAPfxdate field is, but if you need assistance, post the app and I can assist

[vikasmahajan]

you can use Interval Match please find attached demo.

Vikas

[sifatnabil]

Thanks, I actually just used an ApplyMap to replace the date with the exchange rate, instead of a join. The only thing lacking was the date format - so I changed that using your MonthStart suggestion which worked perfectly.