How to make change of currency exchange rate from one value to monthly exchange rate ?

[paulyeo11]

Hi All

Now i have use load script which i mark // which have fit currency value for example USD is 1.25 , since exchange rate is change monthly , may i know how to convert the script , so that it can read monthly exchange rate not just one value = 1.25 ?

sales:
LOAD
'PMC' as SOURCE,
[Row Total],
[Price Currency],
[Price Currency] as CURRENCY,
// ([Row Total] * Pick(Match([Price Currency], 'EUR', 'NT', 'USD', 'YEN', 'BAH', 'SGD'), 1.64, (1/24), 1.25, 0.012, 1/24, 1)) as sales,
Quantity AS [quantity],
[Manufacturer Name (Brand)] AS BRAND_RAW,
[Manufacturer Name (Brand)] AS BRAND_,
Date( Date#([Posting Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Product Code] AS PRO_CLASS

FROM
[C:\Users\Paul Yeo\Dropbox\QLIK_SAP\DEVELOPMENT_QV\ask\TDS.xlsx]
(ooxml, embedded labels, table is TDS);

Hope some one can point to me link that i can look out for example.

Paul Yeo

[Vegar]

You could do like this. Change the inline to your currency source

MapMonthCurrency:

Mapping Load Period & Currency, Rate

inline [

Period, Currency, Rate

2019-01-01, YEN, 0.12

2019-02-02, YEN, 0.13

2019-01-01, EUR, 1.64

2019-02-02, EUR,1.65];

sales:

Load

*

[Row Total] * exchangerate as sales

;
LOAD
'PMC' as SOURCE,
[Row Total],
[Price Currency],
[Price Currency] as CURRENCY,

Applymap('MapMonthCurrency',  monthstart([Posting Date]&[Price Currency] as exchangerate,
// ([Row Total] * Pick(Match([Price Currency], 'EUR', 'NT', 'USD', 'YEN', 'BAH', 'SGD'), 1.64, (1/24), 1.25, 0.012, 1/24, 1)) as sales,
Quantity AS [quantity],
[Manufacturer Name (Brand)] AS BRAND_RAW,
[Manufacturer Name (Brand)] AS BRAND_,
Date( Date#([Posting Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Product Code] AS PRO_CLASS

FROM
[C:\Users\Paul Yeo\Dropbox\QLIK_SAP\DEVELOPMENT_QV\ask\TDS.xlsx]
(ooxml, embedded labels, table is TDS);

[rkpatelqlikview]

Dear Paul,

May be you can go head with  interval Match and slowly changing dimension. This will help you with one more source file which is having with rates and start and end dates. 

For your reference. 

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

https://community.qlik.com/t5/QlikView-Documents/IntervalMatch-and-Slowly-Changing-Dimensions/ta-p/1...

[paulyeo11]

Dear rkpatelqlikview

Thank you very much for the link you provide me . So I know there is way to solve my issue. I know will try to study in detail .

Paul Yeo

[paulyeo11]

Hi Vegar

Thank you for sharing and also thank you very much for provide me with sample code. I have one question. If my raw date in Jan 2019 almost every day have USD transaction, May I know does it mean that I must indicate daily exchange rate ? It is possible use single exchange rate that is 1 st Jan 2019 exchange for compute the whole month jan sales ? So my in line load scrip for one year have 12 row, that is one month one row. It is possible?

Paul

[Vegar]

You can choose to have monthly currency exchange rates.You just need to make sure that all the january transactions are connected to the monthly currency exchange data.  One way to ensure this is to create a exchange table with currency code and a year-month field. Then in the transactions data create a field based on date that matches your year-month field, 

[paulyeo11]

Hi Sir

Thank you for your sharing. I will try it out.

Paul Yeo