Match differente spreadsheets to calculate the exchange rate

[#1]

Dears,

How can I match 2 different tables with the same field called (DATE) in order to get the EXCHANGE RATE (at the secound spreadsheet)... Follow is my example:

At the Spreadsheet 1 I have the following fields: Date, Value, Currency

At the SpreadSheet 2 I have the following fields: Date: Currency, Exchange Rate

And I would like to calculate the total sales, but since I have different currencies I can't easly sum the field (Value). I must do Value * Exchange Rate for the same currency. Actually I'm working with 3 different currencies.

But this Exchange Rate is completely different from days to days. So, how can I do it?

Thanks for your attention,

[Jason_Michaelides]

In your script:

ExchangeRates:

LOAD

     Date,

     Currency,

     [Exchange Rate],

     Date & '/' Currency     AS     Key

FROM ExchangeRateData...;

Transactions:

LOAD

     Date & '/' Currency     AS     Key,

     Value

FROM TxData...;

Then, in your charts you can use SUM(Value*[Exchange Rate])

NOTE:  This assumes that ALL dates and currencies that might occur in the Transactions table are present in the ExchangeRate table.

Hope this helps,

Jason

[#3]

It didn't work!

[#4]

Follow bellow is my Script Editor. Could you please check what is going on?

Thanks for your attention

// Load my main Database.
Labor:
LOAD [F1],
[Claim Close Date] as [Claim Close Date 2],
[Currency] as [Currency 2],
 
[Claim Close Date] & '/' & [Currency] AS Key,
 
FROM (biff, header is line, embedded labels, table is Report$);

// Load the worksheets with currency

Moedas:
LOAD [Claim Close Date] & '/' & [Currency] AS Key,
  [Rate],
  [Claim Close Date],
  [Currency] 
FROM (biff, embedded labels, table is Ratedates$);

[Jason_Michaelides]

Can you post your app at all?

[#6]

Here it is!

FYI:

[Claim Close Date] is the date field

[Currency] is the currency field

end at the end of the script I have the following code:

Moedas:

LOAD Date,

  [BRL],

  [EUR],

  [USD]

FROM (biff, header is line, embedded labels, table is Exchange$);

Where: BRL, EUR and USD contains the exchange rate per day listed on Date...

The data are like:

Date          BRL    EUR  USD

14/05/12     1,8     1,3     1

Thank you so much for your attention

[Jason_Michaelides]

I'm really sorry but I can't open this right now as I am not at home or in the office.  Something's not right though - can you post your data source files and your whole script? Otherwise I'll try and look at it tomorrow.