September 10, 2011 at 11:14 pm
Hi,
I have been cracking my head on this and need some help on currency conversion calculations from two tables here.
Fact table
ACCOUNT | UNIT | CURRENCY | AMOUNT | PERIOD | YEAR | DATASOURCE
-----------------------------------------------------------------------
1001 | SWH | SGD | 10.00 | 06 | 2011 | Source
1001 | SWH | USD | 8.00 | 06 | 2011 | Translated
1001 | SWH | USD | 8.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.00 | 06 | 2011 | Translated
1001 | SWH | USD | 15.00 | 06 | 2011 | Source
1001 | SWH | SGD | 80.00 | 06 | 2011 | Translated
Exchange Rate table
FOREIGN_CURR | LOCAL_CURR | PERIOD | YEAR | VALUE
------------------------------------------------------
MYR | MYR | 06 | 2011 | 1.0000
SGD | MYR | 06 | 2011 | 2.4195
USD | MYR | 06 | 2011 | 3.0260
Based on the Fact table above, I need to convert the amount from foreign currency to local currency only for those records which DATASOURCE = Translated. The exchange rate value is stored in a rate table. Below is the desired output:
Result view
ACCOUNT | UNIT | CURRENCY | AMOUNT | PERIOD | YEAR | DATASOURCE
-----------------------------------------------------------------------
1001 | SWH | SGD | 10.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.21 | 06 | 2011 | Translated //comment: 8.00*3.0260
1001 | SWH | USD | 8.00 | 06 | 2011 | Source
1001 | SWH | MYR | 24.00 | 06 | 2011 | Translated //comment: 24.00*1.0000
1001 | SWH | USD | 15.00 | 06 | 2011 | Source
1001 | SWH | MYR | 193.56 | 06 | 2011 | Translated //comment: 80.00*2.4195
Can anyone help me on how to calculate the Result view? Your kind help is greatly appreciated.
THANKS!
September 11, 2011 at 3:03 am
Duplicate post. Already answered here
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply