Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator III

Sql to qlkview

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

8 Replies
petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable

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...

josephinetedesc
Creator III
Creator III
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

josephinetedesc
Creator III
Creator III
Author

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

buzzy996
Master II
Master II

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)

josephinetedesc
Creator III
Creator III
Author

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

buzzy996
Master II
Master II

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 .