Currency translation

[#1]

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

[sorrakis01]

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 · Accepted solution]

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

[maxgro]

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]

Hi Johannes,

See the example attached.

HTH

Regards,

[#6]

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!