Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master 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
Master III
Master III
Author

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

View solution in original post

13 Replies
amit_saini
Master III
Master III
Author

sunindiaAvinasheliteswuehljagan

Any suggestion???

Thanks
AS

sunny_talwar

Hey Amit,

What is the expected output?

swuehl
MVP
MVP

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
Master III
Master III
Author

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

swuehl
MVP
MVP

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)

sunny_talwar

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

amit_saini
Master III
Master III
Author

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

Thanks,

AS

swuehl
MVP
MVP

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];

sunny_talwar

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