Mapping currency exchange

[#1]

Hello,

I was wondering if someone can help me.

I currently have a data base, with information about different purchases.

Heres an exmaple of the relevant information.

ID	Date	Amount	Currency
1	01/01/2015	120	CAD
2	05/01/2015	500	EUR
3	06/01/2015	336	SGD
4	02/02/2015	1587	EUR
5	20/01/2015	542	GBP
6	31/01/2015	24	CAD
7	05/02/2015	126	GBP

Besides i have a data base, that has the currency rate per month. It turn everything to dollars. Here is and example:

Currency1	Currency2	Date	Exchange rate
CAD	USD	01/2015	1.243
EUR	USD	01/2015	0.913
CAD	USD	02/2015	1.256
EUR	USD	02/2015	0.911

Here i just showed an example, but both data bases are bigger, and the point is i need to report all my results on USD.

Si i was wondering how i can create an additional field on my first db, so i can add the exchange rate for that purchase.

I need to map this using the date (that is only by month) and the currency.

Is there a way to do this??

I cant just add em and use a sync table because of other elements on the application.

Im looking for a solution inside the loading code.

thanks,

[#2 · Accepted solution]

Try this:

Temp:

LOAD      ID,

                Date,

                Amount,

               Currency

FROM

     Table1;

INNER JOIN (Temp)

LOAD

     Currency1 AS Currency,

     Date,

     [Exchange rate]

FROM

     Table2;

Regards!

Marcos Freire

[swuehl]

You can create a field in your first table to repesent the month, with the same value format as in the second table, maybe like

FACT:
LOAD *,

     Right(Date, 7) as Month

RESIDENT YourFirstTable;

LEFT JOIN (FACT)

LOAD Currency1 as Currency,

           Date as Month,

          [Exchange Rate]

Resident YourExchangeRateTable;

DROP FIELD Month;