topic Re: Lookup in another table ?? in QlikView

Lookup in another table ??

Thu, 19 Mar 2015 09:20:56 GMT

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

Re: Lookup in another table ??

PrashantSangle — Thu, 19 Mar 2015 09:27:09 GMT

Hi,

You can use Exist()

Regards

Re: Lookup in another table ??

sujeetsingh — Thu, 19 Mar 2015 09:30:25 GMT

Join the tables and use exist condition .

Re: Lookup in another table ??

Roop — Thu, 19 Mar 2015 09:40:12 GMT

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

Re: Lookup in another table ??

Thu, 19 Mar 2015 09:45:24 GMT

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

Re: Lookup in another table ??

Thu, 19 Mar 2015 09:49:45 GMT

Hi Rupert

Thanks for the reply.

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

/Teis

Re: Lookup in another table ??

Roop — Thu, 19 Mar 2015 09:54:18 GMT

See Help under

IntervalMatch

and

IntervalMatch (Extended Syntax)

Re: Lookup in another table ??

Thu, 19 Mar 2015 10:23:06 GMT

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

Re: Lookup in another table ??

Peter_Cammaert — Thu, 19 Mar 2015 11:05:34 GMT

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

Re: Lookup in another table ??

Roop — Thu, 19 Mar 2015 11:12:46 GMT

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)

Re: Lookup in another table ??

Thu, 19 Mar 2015 11:15:10 GMT

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

Re: Lookup in another table ??

Thu, 19 Mar 2015 12:04:52 GMT

Hi Rupert

Thanks again, the script runs smooth now

Have a pleasant day.

/Teis