topic Re: Complex SQL Query turned into QVD Query in QlikView

Complex SQL Query turned into QVD Query

marcel_olmo — Mon, 08 Jul 2013 14:30:19 GMT

Hi guys, I have a query like this :

SELECT

TABLE1.FIELD1,

TABLE2.FIELD2,

TABLE3.FIELD3,

....

FROM

TABLE1,

TABLE2,

TABLE3,

....

WHERE

TABLE1.FIELD1 = TABLE2.FIELD1

AND TABLE1.FIELD1 = TABLE3.FIELD1

....

It's a very complex query which makes the computer memory go upper their limit. So it collapses.

In order to avoid this, I decided to create different qvds from the different tables I want to relate to. Once I have the QVD tables, I'd can replicate the same query as the SQL query showed before.

These are my QVD tables:

Table1.qvd, Table2.qvd, Table3.qvd, etc.....

There are a lot of tables and a lot of fields to join. My point is --> How to avoid loading first the qvd tables in memory to make the joins after?

I'd like to replicate the same syntax as SQL with the QVD way.

Anyone knows how to do it?

Many thanks in advance!

Regards, Marcel.

Re: Complex SQL Query turned into QVD Query

Miguel_Angel_Baeyens — Mon, 08 Jul 2013 16:24:03 GMT

Hi Marcel,

What about loading each field beforehand then use the EXISTS() clause?

Table1:
LOAD Field1
FROM Table1.qvd (qvd);
Table2:
LOAD Field2
FROM Table2.qvd (qvd);
  
Table3:
LOAD Field3
FROM Table3.qvd (qvd);
TableFull:
LOAD *
FROM *.qvd (qvd)
WHERE Exists(Field1, Field2) AND Exists(Field2, Field3);

Hope that helps.

Miguel

Re: Complex SQL Query turned into QVD Query

marcel_olmo — Mon, 08 Jul 2013 16:29:01 GMT

Thanks Miguel Angel for your quick answer.

My point is that in my original SQL Query there are a lot of complex stuff like where field1 in (select distinct field1 from table2), and things like this.

Why I'm not loading my Table directly from the SQL query? Because the query is too heavy and I cannot do it right now.

That's why I decided first of all download all the related tables in qvds.

The perfect way would be a syntax as similar as possible as the SQL Query, but I think maybe this is not possible.

If this is not possible, I'll try by your way.

Regards, Marcel.

Re: Complex SQL Query turned into QVD Query

rbecher — Mon, 08 Jul 2013 16:59:04 GMT

Hi Marcel,

you have to reset your mind to code similar things in QlikView script. You cannot use SQL-like code if you want to make it with low memory footprint AND high performance..

But, there is always a way doing it right even if the SQL is complex. Btw. I guess it's not complex with only three tables..

- Ralf