topic Re: Currency Exchange Help!!! in QlikView

Currency Exchange Help!!!

amit_saini — Tue, 29 Mar 2016 11:28:11 GMT

Hi Folks,

Attached is my sample application.

From vertical list you can see , we are having some currencies and on text box there corresponding exchange rate.

Now what I'm looking here below in table is to show all values in USD , doesn't matter from which particular plant values are coming ,

table should show you values in USD only!

Please help for the solution!

Thanks

Re: Currency Exchange Help!!!

amit_saini — Tue, 29 Mar 2016 12:16:00 GMT

sunindia‌ Avinashelite‌swuehl‌jagan‌

Any suggestion???

Thanks
AS

Re: Currency Exchange Help!!!

sunny_talwar — Tue, 29 Mar 2016 12:19:39 GMT

Hey Amit,

What is the expected output?

Re: Currency Exchange Help!!!

swuehl — Tue, 29 Mar 2016 12:29:48 GMT

Your currency table shows multiple exchange rates per currency (rates on different dates).

So you would need to clarify how you want to calculate your dollar amounts.

Also, your fact table shows some fields that are names like 'Net Dollar Value of Inventory Adjustments'

Are these already in USD or in some other currency.

Best, post a detailed description of you data model and requirements

Re: Currency Exchange Help!!!

amit_saini — Tue, 29 Mar 2016 12:37:59 GMT

Hi,

As of now the data inside table is in Local currency and the requirement is to show all data in USD.

Just for example suppose "KCC" is having local currency as "CNY" and conversion from CNY to USD based on latest date is 8.28

so that output should be like attached , all numbers has to multiply by 8.28 , so that I can get figures in USD.

Background:

All the data in local currency I'm getting from Excel sheets and for exchange rate I'm using SAP TCURR table

TCURR:

LOAD //MANDT,

KURST,

FCURR,

TCURR as [Local currency],

(99999999-GDATU) as Latest_date,

UKURS,

FFACT,

TFACT

FROM

D:\qvprod\qvd\SAP\KTX_100\TCURR.qvd

(qvd)

where KURST='M' and FCURR='USD' and match(TCURR,'BRL', 'CNY' ,'CZK' ,'EUR', 'GBP', 'INR','YEN');

This below is the expresion for getting latest exchange rate based on current date:

Only({<Latest_date={$(=Max(Latest_date))}>} (UKURS))

Thanks,

Re: Currency Exchange Help!!!

swuehl — Tue, 29 Mar 2016 12:45:55 GMT

Any reason you need to keep all the historic exchange rates?

Or can you flag the latest date per currency in the script, i.e. the rate you want to use for your calculations?

Then your expression might look like

=sum({<LatestFlag = {1}>} [Value of material before counting] * UKURS)

Re: Currency Exchange Help!!!

sunny_talwar — Tue, 29 Mar 2016 12:48:36 GMT

In addition why not do the currency conversion in the script and create a field which is purely in USD?

Re: Currency Exchange Help!!!

amit_saini — Tue, 29 Mar 2016 13:24:05 GMT

Sorry not sure how to get that script , please help if you can!

Thanks,

Re: Currency Exchange Help!!!

swuehl — Tue, 29 Mar 2016 13:27:51 GMT

For the flag, you can use something like

TCURR:

LOAD //MANDT,

KURST,

FCURR,

TCURR as [Local currency],

(99999999-GDATU) as Latest_date,

UKURS,

FFACT,

TFACT

FROM

D:\qvprod\qvd\SAP\KTX_100\TCURR.qvd

(qvd)

where KURST='M' and FCURR='USD' and match(TCURR,'BRL', 'CNY' ,'CZK' ,'EUR', 'GBP', 'INR','YEN');

LEFT JOIN (TCURR)

LOAD

[Local currency],

Max([Latest_date]) as [Latest_date],

1 as LatestFlag

RESIDENT TCURR

GROUP BY [Local currency];

Re: Currency Exchange Help!!!

sunny_talwar — Tue, 29 Mar 2016 13:30:22 GMT

Do not have the source data files, but the idea is like this:

Fact:

LOAD Price,

Value,

[Local Currency]

FROM Source;

Left Join (Fact)

LOAD Currency as [Local Currency],

[Exchange Rate],

FROM Currency

Where "Use a where statement to restrict the currency exchange rates for the max date here"

FinalFact:

LOAD Price * [Exchange Rate] as [Price in USD],

Value * [Exchange Rate] as [Value in USD],

...

Resident Fact;

DROP Table Fact;

You can also use ApplyMap() instead of Left Join which might give you a better performance

Re: Currency Exchange Help!!!

amit_saini — Wed, 30 Mar 2016 05:15:25 GMT

Thanks Sunny!

I tried with simple if statement and got correct output.

if(Match(Plant,'KCC','KCQ','KGZ2','KSH','KWH'),sum([Value of material before counting])*0.15292 ,if(Match(Plant,'KJP'),sum([Value of material before counting])*0.00878 ,if(Match(Plant,'KKI'),sum([Value of material before counting])*0.01485,

if(Match(Plant,'HQE','KBK','KBL','KISA','KKS','KOF','KWK','KDU'),sum([Value of material before counting])*1.09025 ,if(Match(Plant,'KBO','KBO2'),sum([Value of material before counting])*0.04150,

if(Match(Plant,'KBR'),sum([Value of material before counting])*0.23788 ,if(Match(Plant,'KHE','KUK'),sum([Value of material before counting])*1.40750,sum([Value of material before counting]))))))))

Thanks,

Re: Currency Exchange Help!!!

sunny_talwar — Wed, 30 Mar 2016 08:20:06 GMT

Awesome Amit.

But aren't these exchange rates going to change each period? If they do, are you planning to change your expression each period?

Re: Currency Exchange Help!!!

amit_saini — Wed, 30 Mar 2016 08:51:33 GMT

Hi Sunny ,

We do have Exchange rate sheet handled by Finance team , so instead manual work , I will link excel sheet for exchange rates .

Thanks,

Re: Currency Exchange Help!!!

sunny_talwar — Wed, 30 Mar 2016 08:52:34 GMT

That make sense