Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor III

Currency Exchange Help!!!

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

AS

1 Solution

Accepted Solutions
amit_saini
Honored Contributor III

Re: Currency Exchange Help!!!

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,

AS

13 Replies
amit_saini
Honored Contributor III

Re: Currency Exchange Help!!!

sunindiaAvinasheliteswuehljagan

Any suggestion???

Thanks
AS

Re: Currency Exchange Help!!!

Hey Amit,

What is the expected output?

MVP
MVP

Re: Currency Exchange Help!!!

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

amit_saini
Honored Contributor III

Re: Currency Exchange Help!!!

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,

AS

MVP
MVP

Re: Currency Exchange Help!!!

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

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

amit_saini
Honored Contributor III

Re: Currency Exchange Help!!!

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

Thanks,

AS

MVP
MVP

Re: Currency Exchange Help!!!

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

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

Community Browser