4 Replies Latest reply: Jan 5, 2012 4:43 PM by Thor Hansen RSS

    Fetching exchange rates from a different table, creating a new field with a calculation

      Hi guys,

       

      I have two tables, one containing exchange rates for all companies (Blue table) and one containing all Ledger Transactions (Red Table).

      The ledger transaction table does not include a secondary currency, which I would like to add a field for manually, and calculate the value.

       

      The issue is, that in the Exchange Rate table, I have exchange rates dating back to 2007, and I have them for all companies in our ERP system, defined in but the two digits in the last column.  What I want to do, is to base my calculation on the last know value for each currency, but only in Company 10 (last blue column).

       

      So, if I add a new field to my LedgerTransactions table called Secondary currency, how can I do the following calculation based on the tables below:

       

      (AMOUNTCUR in LEDGER TRANSACTIONS) * (LOOKUP LATEST CURRENCY VALUE FROM EXCHANGE RATES TABLE FOR COMPANY 10) / 100 = 910 146

       

      And for the second row:

       

      AMOUNTCUR * (692,34) /100 = 1 038 510

       

      Regards

      Thor

       

      2012-01-05_10-58-10.jpg

        • Fetching exchange rates from a different table, creating a new field with a calculation

          Hello Thor,

           

          check the following code snippets:

          first I'd create (out of EXCHANGE RATES TABLE) a Lookup-Table only containing valid ExRates with the MaxDates.

          Note the right Join via FromDate. With it we will have only maxdates left.

           

          LOAD

               *

          From [EXCHANGE RATES TABLE]

          ;

          Right Join

          LOAD

              Max(Date(FromDate))    AS FromDate,

              Curr,

              Company

          Resident [EXCHANGE RATES TABLE]

          Where Company = 10

          Group By Company, Curr

          ;

           

           

          Second step is a Lookup() to create then new Field while loading [LEDGER TRANSACTIONS]

          LOAD

               . . .

              Lookup('Rate', 'Curr', CURRENCY) /* and the rest of your formula */ AS SEC_CURR

          From [LEDGER TRANSACTIONS]

          ;

           

          Hope this Helps

          Roland

            • Fetching exchange rates from a different table, creating a new field with a calculation

              Thanks Roland,

               

              I understand what you want me to do, but I cannot get it working, and it is probably because I am trying to re-write is for an SQL load.

               

              Right Join
              LOAD MAX(DATE(FROMDATE)) as FromDate,
                  EXCHRATE,
                  CURRENCYCODE,
                  DATAAREAID;
              SQL SELECT *
              FROM Dynamics2009.dbo.EXCHRATES Resident [EXCHRATES] and Where DATAAREAID = 10 and Group By DATAAREAID, CURRENCYCODE;

               

              The second part, I do not understand how it will work.

               

              This is how my LedgerTrans LOAD looks:

               

              LOAD ACCOUNTNUM,
                 
              TRANSDATE,
                 
              year(TRANSDATE) as YEAR,
                 
              month(TRANSDATE) as MONTH,
                 
              year(TRANSDATE) as TransYear, // this is what you know right now
                if (year(TRANSDATE) <= Year(Today()), year(TRANSDATE))    AS SelectYear,   // new field
                  AMOUNTMST,
                 
              AMOUNTCUR,
                 
              CURRENCYCODE,
                 
              DIMENSION as Department,
                 
              "DIMENSION2_" as Person,
                 
              "DIMENSION3_" as Segment,

                  "DIMENSION4_" as Partner,
                 
              "DIMENSION5_" as Product;
              SQL SELECT *
              FROM Dynamics2009.dbo.LEDGERTRANS;

                • Fetching exchange rates from a different table, creating a new field with a calculation

                  OK Thor,

                  now I am able to redesign and comment your scripting:

                   

                  //load EXCHRATES from SQL-Server as source:

                  EXCHRATES:

                  SQL SELECT

                      FROMDATE ,

                      EXCHRATE,

                      CURRENCYCODE

                  FROM Dynamics2009.dbo.EXCHRATES

                  Where DATAAREAID = 10

                  ;

                  // next do a QV-Join, this is NOT a SQL-Join:

                  // input is the (resident)  QV-Table just loaded

                  Right Join

                  LOAD

                      Max(Date(FROMDATE))    AS FROMDATE,

                      CURRENCYCODE

                  Resident EXCHRATES

                  Group By CURRENCYCODE

                  ;

                  //now Load the Ledger Table

                  // I recognized some lines

                  LOAD ACCOUNTNUM,
                     
                  TRANSDATE,

                      . . .,

                  //here sth similar to this:

                     Lookup('EXCHRATE', 'CURRENCYCODE', CURRENCYCODE) /* and the rest of your formula */ AS SEC_CURR,

                  . . .

                  ;

                  SQL SELECT *
                  FROM Dynamics2009.dbo.LEDGERTRANS;

                   

                   

                  Regards, Roland

                   

                  Note:

                  Didn't check the syntax.

                    • Fetching exchange rates from a different table, creating a new field with a calculation

                      You are brillian!! 

                       

                      It did not work straight away, but after modifying the lookup to include the table name as well, it worked like I charm.  Do you do paid online consultancy? 

                       

                      Thanks again,

                       

                      /Thor

                       

                      EXCHRATES:
                      SQL SELECT
                          FROMDATE ,
                          EXCHRATE,
                          CURRENCYCODE
                      FROM Dynamics2009.dbo.EXCHRATES
                      Where DATAAREAID = 10;

                       


                      Right Join
                      LOAD
                          Max(Date(FROMDATE))AS FROMDATE,
                          CURRENCYCODE
                      Resident EXCHRATES
                      Group By CURRENCYCODE;

                       

                      LOAD ACCOUNTNUM,
                          TRANSDATE,
                          year(TRANSDATE) as YEAR,
                          month(TRANSDATE) as MONTH,
                          year(TRANSDATE) as TransYear,
                                VOUCHER,
                          TXT,
                          AMOUNTMST,
                          AMOUNTCUR,
                          CURRENCYCODE,
                          Lookup('EXCHRATE', 'CURRENCYCODE', CURRENCYCODE, 'EXCHRATES') AS SEC_CURR,
                          DIMENSION as Department,
                          "DIMENSION2_" as Person,
                          "DIMENSION3_" as Segment,
                          DATAAREAID,
                          RECVERSION,
                          RECID,
                          "DIMENSION4_" as Partner,
                          "DIMENSION5_" as Product;
                      SQL SELECT *
                      FROM Dynamics2009.dbo.LEDGERTRANS;