Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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

View solution in original post

5 Replies
sorrakis01
Specialist
Specialist

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

Gysbert_Wassenaar

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
maxgro
MVP
MVP

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
Creator III
Creator III

Hi Johannes,

See the example attached.

HTH

Regards,

Not applicable
Author

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!