topic Re: Load data from database in QlikView

Load data from database

Anonymous — Mon, 06 Aug 2012 14:28:26 GMT

Hi,

I like to load from a big table only the records with a special value (e.q. =Post_type=6). I use the load en select statement withe the where clausule and order by like:

Load *

....

select *from part

where post_type=6 order by post_type;

This works, but QV read all the rekords and it takes a lot of time. Ik would like qv to stop after read and load the records which i want.

How do i get QV to stop the load after loading the wanted record and not to read the whole table ?

Re: Load data from database

Mon, 06 Aug 2012 14:36:20 GMT

LOAD *;
SQL SELECT * from part Where post_type=6 order by post_type;

Should work. Since the qlikview will only execute SQL statement which will process with the condition. You confirm this from the log file.

Regards,

Kiran Rokkam.

Re: Load data from database

bgerchikov — Mon, 06 Aug 2012 19:16:10 GMT

Hi,

I'd recommend do not use Order By clause in the load SQL statement (I'm not clear why do you need it?). Please use temporary table for initial load instead. Order By might be very costly operation on the back end side.

In addition, you might need to setup index in your DB on post_type field to speed up the process. If you cannot do this, just load everything.

Temp:

LOAD *; SQL SELECT * from part;

NewTable:

NoConcatenate Load * Resident Temp

Where

post_type=6

order by post_type;

Drop table Temp;

Re: Load data from database

jonathandienst — Mon, 06 Aug 2012 20:05:23 GMT

As Kiran said, QV will only process the records that meet the where condition, but the "order by' clause could be hurting performance if the source table is large. Use Kiran's script, without the order by

LOAD *; SQL SELECT * from part Where post_type=6;

Also do you need all the fields in the table? If not, then it would be better to name the fields in the SQL SELECT statement, rather than using SELECT *.

Regards

Jonathan