Currency Code mapping

[#1]

Hi

Within our database someone set up the Australian Dollar as currency code as AUS instead of AUD.

When I load currency rates from OANDA.

left join LOAD Currency, Code as CurrencyJoin, num(trim([EUR/1 Unit])) as Eurorate, trim([Units/1 EUR]) as InvRate FROM [http://www.oanda.com/convert/fxdaily?redirected=1&exch=EUR&format=HTML&dest=GET+CUSTOM+TABLE&sel_list=GBP_EUR_USD_JPY_NOK_CHF_AUD_JPY_CAD_RUB&format=HTML&redirected=1] (html, ansi, embedded labels, table is @1);

Its not an option to change from AUS to AUD within our database as there are so many tables it would need changing that the potential to miss would cause more issues than a missing currency rates in QV.

I hope that there is a smart workaround that someone can share with me and would appreciate your wisdom.

Thanks

Rob

[hector]

Hi,

did you try an unification table? i mean a table which unifies AUS and AUD -> AUD, so you are not going to manipulate the original data

Ex

If you have your source date (t1), first join a table with this structure

OriginalCurrencyCode	UnifiedCurrencyCode
USD	USD
AUS	AUD
AUD	AUD

Then join the oanda table but linking the second column as the key, the problem with this solution is the creation of this unification table, because you need to know all the values, the good ones (like USD in the example), and the bad ones (like AUS and AUD)

Hope this helps

Rgds

[#3]

Thanks for the quick reply,

I was trying to avoid that given I was left joining the data into another table. I was stumbling around the forums and thinking along the lines of a mapping load but never used one before. Am I going down the wrong alley.

I'll certainly give have a go at your suggestion and I am sure it would work, given I know most of the currencies that we have ever billed in.

Thanks again

Rob

[hector]

Hi,

if your concern it's about duplicating records, check this solution, it uses mapping load and it's better than the previous suggested by me, because only you need to create the wrong currency values and not all of them

Check the file and tell us.

Rgds