13 Replies Latest reply: Mar 30, 2016 4:52 AM by Sunny Talwar RSS

    Currency Exchange Help!!!

    Amit Saini

      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

        • Re: Currency Exchange Help!!!
          Amit Saini

          stalwar1 avinasheliteswuehljagan

           

          Any suggestion???

           

          Thanks
          AS

            • Re: Currency Exchange Help!!!
              Sunny Talwar

              Hey Amit,

               

              What is the expected output?

              • Re: Currency Exchange Help!!!
                Stefan Wühl

                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

                    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

                      • Re: Currency Exchange Help!!!
                        Stefan Wühl

                        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

                            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

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

                                 

                                Thanks,

                                AS

                                  • Re: Currency Exchange Help!!!
                                    Stefan Wühl

                                    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

                                      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