5 Replies Latest reply: Apr 30, 2015 3:05 AM by Johannes Mathis

# Currency translation

Hi Community,

I'm a very beginner in QV and I have a question regarding currency translation.

I have two tables which I hvae connected via "Local Currency" and "Currency".

Table1

Invoice Number

Amount

Document Currency

Document CurrencyLocal Currency
1200USDCHF
2150EURCHF
3300CHFCHF
435000THBCHF
5700USDEUR

Table2

Currencyfx-rate BU vs EUR
CHF1,3
USD0,9
THB40
EUR1

Now in the script I want QV to create an new dimension "Amount DC @ LC" by calculating the "Amount Document Currency" translated in Local Currency @ BU fx-rate.

I would like to have the following result:

Invoice NumberNEW: Amount DC @ LCLocal Currency
1288,89 (=200/0,9*1,3)CHF
2195 (=150/1*1,3)CHF
3 300 (=300/1,3*1,3)CHF
41137,5 (=35000/40*1,3)CHF
5 777,78 (=700/0,9*1)EUR

How is it possible?

Thanks and kind regards

Johannes

• ###### Re: Currency translation

HI,

Currencys:

Mapping LOAD Currency as [Local Currency],

[fx-rate BU vs EUR] as EUR

FROM

[C:\Users\jarenas\Desktop\Test\Libro1.xlsx]

(ooxml, embedded labels, table is Hoja3, filters(

Remove(Row, Pos(Top, 6))

));

Final_table:

EUR * Amount AS New_Value;

ApplyMap('Currencys',[Local Currency],'N/D') As EUR

FROM

[C:\Users\jarenas\Desktop\Test\Libro1.xlsx]

(ooxml, embedded labels, table is Hoja2);

Regards

• ###### Re: Currency translation

You can use a mapping table and the applymap function:

```MapCurrency:
Currency,
[fx-rate BU vs EUR ]
FROM ...

Invoices:
[Invoice Number],
[Amount Document Currency],
[Document Currency],
[Local Currency],
[Amount Document Currency]
/ applymap('MapCurrency',[Document Currency],1)
* applymap('MapCurrency',[LocalCurrency],1) as [NEW: Amount DC @ LC]
FROM ...

```
• ###### Re: Currency translation

e:

LOAD Currency,     [fx-rate BU vs EUR]

FROM

(html, codepage is 1252, embedded labels, table is @2);

f:

LOAD [Invoice Number],     [AmountDocument Currency],     [Document Currency],     [Local Currency]

FROM

(html, codepage is 1252, embedded labels, table is @1);

left join (f)

load Currency as [Document Currency],     [fx-rate BU vs EUR] as [Document Currency X]

Resident e;

left join (f)

load Currency as [Local Currency],     [fx-rate BU vs EUR] as [Local Currency X]

Resident e;

left join (f

load *,  [AmountDocument Currency] / [Document Currency X] * [Local Currency X] as NewAmount

Resident f;

• ###### Re: Currency translation

Hi Johannes,

See the example attached.

HTH

Regards,

• ###### Re: Currency translation

Thanks to all of you!

I use Gysberts solution. For my needs it is fine.

The solution from Alex is perfect, but I was not able to "transalte" it into my script. Maybe For sure I need more experience with QV!