Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
you can use Interval Match please find attached demo.
Vikas
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.