Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Complex SQL Query turned into QVD Query

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.

Tags (4)
3 Replies
Highlighted

Re: Complex SQL Query turned into QVD Query

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

Highlighted
Partner
Partner

Re: Complex SQL Query turned into QVD Query

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Complex SQL Query turned into QVD Query

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

Vizlib Head of R&D