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

[#1]

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

[#2]

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

[#3]

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;

[#4 · Accepted solution]

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.

[#5]

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;