Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have just read the document Created by FABRICE44 on Jan 16, 2014 regarding loading into QlikView.
My question might be more sql related rather than QlikView.
I have a script that produces a view in a live production database. This view is then loaded into QlikView.
Should I use the "where" in sql rather than qlikView?
Jo
The answer should normally be yes to that one. But it depends on which effect the where clause has on execution. It might speed up execution (speed of row delivery from the SQL) but it might also seriously degrade performance. So a quick test is a smart thing to do. If the speed is degrading then you should talk to those that might be able to tune the database in some way. Often that means creating additional indexes in the database to support an optimal query.
Sometimes a simple where clause in your LOAD is better.
But in principle you should stick to the rule that removing unneeded data as early as possible is smart logistics.
As everything in Qlik - it depends...
I prefer to avoid using views altogether. The problems with views:
- they are slow;
- if you have a multiple copies of databases, you must create the same views in each;
- it requires database privileges which Qlik developer not always have.
Instead of the views, you can create QVDs that replicate the same views, and load data into your apps from the QVDs.
Speaking of the where clause, I assume you mean this:
LOAD
...
WHERE...
;
SQL SELECT
...
FROM ...
WHERE...;
You can use WHERE in any place or in both. If you use it in SQL, it filters out the records on the first load, hence you use less memory. Sometimes it is inconvenient or not possible for different reasons, for example if the condition is WHERE exists() - SQL doesn't understand Qlik functions. When possible, I use it in SQL.
Hope it helps. Let's see more replies...
so to recap
1. try and limit the files you want in SQL,
2. I have now read about qvd files http://www.quickintelligence.co.uk/qlikview-qvd-files/
and I suppose that would be the way forward (when the view is bedded down).
Another question - does the view which produces the table get produced each time the QlkView call is made, that is, I hit reload. Probably too obvious a question ...
Jo
Hi,
Using Where condition in SQL is the best option if possible, it reduces the load on database and also the load time. So wherever possible use WHERE condition in SQL query itself.
Regards,
Jagan.
Another question - does the view which produces the table get produced each time the QlkView call is made, that is, I hit reload. Probably too obvious a question ...
and also
From SQL some columns you actually see NULL - that is text so easy enough to find. Some colums are blank. I seem to be able to find them using if(isText(column) - blanks - is this ok or will there be problems in the future
Another question - does the view which produces the table get produced each time the QlkView call is made, that is, I hit reload. Probably too obvious a question ...
--yes
and also
From SQL some columns you actually see NULL - that is text so easy enough to find. Some colums are blank. I seem to be able to find them using if(isText(column) - blanks - is this ok or will there be problems in the future
---here u can try like if(isnull(urfield),'text here',urfield)
Thank you everyone - I know need to read all about qvd files. Not sure how to do "correct answer" when so many people have contributed
Jo
u can mark,whoever answered first with correct answer to ur query generally,but this case u supposed to mark as correct..whoever answered most of ur queries,I guess .