Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions

Re: Currency translation

You can use a mapping table and the applymap function:

MapCurrency:

MAPPING LOAD

     Currency,

     [fx-rate BU vs EUR ]

FROM ...

Invoices:

LOAD

     [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 ...


talk is cheap, supply exceeds demand
5 Replies
sorrakis01
Valued Contributor

Re: Currency translation

HI,

Currencys:

Mapping LOAD Currency as [Local Currency],

     [fx-rate BU vs EUR] as EUR

FROM

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

Remove(Row, Pos(Top, 6))

));

Final_table:

LOAD *,

EUR * Amount AS New_Value;

LOAD *,

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

FROM

(ooxml, embedded labels, table is Hoja2);

Regards

Re: Currency translation

You can use a mapping table and the applymap function:

MapCurrency:

MAPPING LOAD

     Currency,

     [fx-rate BU vs EUR ]

FROM ...

Invoices:

LOAD

     [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 ...


talk is cheap, supply exceeds demand
MVP
MVP

Re: Currency translation

1.png

e:

LOAD Currency,     [fx-rate BU vs EUR]

FROM

[https://community.qlik.com/thread/162199]

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

f:

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

FROM

[https://community.qlik.com/thread/162199]

(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;

alex_millan
Contributor III

Re: Currency translation

Hi Johannes,

See the example attached.

HTH

Regards,

Not applicable

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!

Community Browser