Optimised Load with Where

jindrichk — Mon, 22 Apr 2013 13:56:31 GMT

Hi,

I would like to load a data from QVD but use filtering and keep it optimised. I know there is only one way how to do it when using WHERE clause and it is via EXISTS.

Let say we have this simple QVD named TableSource.qvd:

TableSource
LOAD
     *
INLINE [
     TableData1, TableData2, TableData3
     'A', 1, 1
     'B', 2, 0
     'A', 4, 1
     'C', 3, 0
     ]
STORE
     *
FROM
     TableSource
INTO
     TableSource.qvd (qvd);

I would like to load to table "TableResult" data from this QVD which has "A" in the column TableData1.

I tought I should be able to do something like this

TableResult:
LOAD
     *
FROM
     TableSource.qvd (qvd)
WHERE
     exists(TableData1, 'A');

or use the similar logic with third flag column. But it doesn't work.

I found a way using the INNER JOIN but it is without WHERE. I'm sure there has to be way using WHERE clause.

Just for info the optimised way via INNER JOIN is this:

LOAD
     *
INLINE [
     TableData1
     A
     ];
INNER JOIN
     ([TableRestriction])
LOAD
     *
FROM
     TableSource
INTO
     TableSource.qvd (qvd);

Without WHERE it just load all data from TableSource.qvd and filter it aftewards. Using WHERE it filter it when loading rows.

Can anybody see some more suitable way?

Regards,

Jindra

Message was edited by: jindrichk

Re: Optimised Load with Where

Mon, 22 Apr 2013 14:19:43 GMT

You cannot load from one table into another if they are not the same.

You have join you tables on.

A where clause is your filter and have nothing to do with loading from one to another, but it can be beneficial in form of loading correct data.

An exists is checking whether data from A also exists in B. Are they the same you can use an exists and only get ex. the newest data.

And you can just put ur where clause after your join?

topic Optimised Load with Where in QlikView

Optimised Load with Where

Re: Optimised Load with Where