Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Currency | Local Currency |
---|---|---|---|
1 | 200 | USD | CHF |
2 | 150 | EUR | CHF |
3 | 300 | CHF | CHF |
4 | 35000 | THB | CHF |
5 | 700 | USD | EUR |
Table2
Currency | fx-rate BU vs EUR |
---|---|
CHF | 1,3 |
USD | 0,9 |
THB | 40 |
EUR | 1 |
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 Number | NEW: Amount DC @ LC | Local Currency |
---|---|---|
1 | 288,89 (=200/0,9*1,3) | CHF |
2 | 195 (=150/1*1,3) | CHF |
3 | 300 (=300/1,3*1,3) | CHF |
4 | 1137,5 (=35000/40*1,3) | CHF |
5 | 777,78 (=700/0,9*1) | EUR |
How is it possible?
Thanks and kind regards
Johannes
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 ...
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
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 ...
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;
Hi Johannes,
See the example attached.
HTH
Regards,
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!