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);

[PrashantSangle]

Hi,

You can use Exist()

Regards

[sujeetsingh]

Join the tables and use exist condition .

[Roop]

As you have a start and end date for the exchange rates I would use IntervalMatch as it will return exactly what you require.

[#5]

Hi Max

Thanks for your reply.

I have try with the exists statement, but do not have the experience to incorporate the criteria with the INVOICEDATE.

Can u help me please?

/Teis

[#6]

Hi Rupert

Thanks for the reply.

How does that work when I have 2 table and wanna join them?

/Teis

[Roop]

See Help under

IntervalMatch

and

IntervalMatch (Extended Syntax)

[#8]

Hi Rupert

Thanks for the answer.

I know the statement, but how I use it in my current example?

I can run it just fine with a loop, It just take to long a time, I am looking for a faster and easier way the run the script

/Teis

[Peter_Cammaert]

The simple INTERVALMATCH will serve you well. Check out the example in the Desktop Help. We can do the thinking for you, but then an example QVW document upload by you would be very helpful to avoid too much guesswork (for example, what's in EXCHANGECODE?)

A much easier method to comprehend/maintain is that you expand your Exchange rate table from a CURRENCY-EXCHANGERATE-FROM-TO layout into a CURRENCY-EXCHANGERATE-DATE layout using the LOAD WHILE construct. The table will grow to cover all individual dates in a selected period (for example, earliest transaction date to newest transaction date) but won't take much memory.

Then the combination of the two tables becomes a simple LEFT JOIN on DATE and CURRENCY.

Best,

Peter

[Roop · Accepted solution]

Something like:

ExchangeRates:

LOAD FROMDATE,

     TODATE,

     EXCHANGECODE,

  EXCHANGERATE

FROM

testcross.xlsx

(ooxml, embedded labels, table is Sheet3);

LOAD INVOICENUMBER,

     INVOICEDATE,

     EXCHANGECODE

FROM

testcross.xlsx

(ooxml, embedded labels, table is Sheet2);

Inner Join IntervalMatch(INVOICEDATE, EXCHANGECODE)

LOAD FROMDATE,

     TODATE,

     EXCHANGECODE

Resident

ExchangeRates;

-----------------------------------------------------

should do it .... (I have used Excel)