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

# 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

• ###### 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;