Lookup in another table ??

[#1]

Hi there

Can u please help me?

For each INVOICENUMBER i wanna join the correct EXCHRATE.

The INVOICEDATE have to be >FROMDATE and <TODATE

Can this be done without looping, the 'INVOICETRANS' table is over 10 m rows.

Thanks in advance

/Teis

/////////////////////////////////////////////////////////////////////

These are my 2 table:

DEBINVTRANS:
LOAD
INVOICENUMBER,
INVOICEDATE,
EXCHANGECODE
FROM
[..\..\..\3.QVD\3.1.Source\3.1.1.SQL\DEBINVTRANS.qvd]
(qvd) where INVOICEDATE >= makedate(2013,1 ,1);


EXCHANGECODERATE:
LOAD
EXCHANGECODE,
EXCHRATE,
FROMDATE,
TODATE
FROM
[..\..\..\3.QVD\3.1.Source\3.1.1.SQL\EXCHANGECODERATE.qvd]
(qvd);

[#2]

Hi Peter

Thanks for your reply:

The field EXCHANGECODE consist of the different country codes.

I will try doing the CURRENCY-EXCHANGERATE-DATE layout u suggest, I think that will work just fin and resource efficiently.

I will report back.

/Teis

[#3]

Hi Rupert

Thanks again, the script runs smooth now

Have a pleasant day.

/Teis