Advice on loading Select data

[#1]

Hi everyone,

I'm relatively new to QV, soo please bear with me.

I've connected to a reasonably large database containing 2.5million+ records with 50 or so columns, and would like to analyse all the records in only 20 or so columns.

Is it best to SELECT all the records for the columns in question first, before performing a series of filtering steps to focus on the subset that I want to analyse, or would it be better to build in all the filters after the select statement. I was hoping to pull in everything and use a tool in the GUI to define the filters.

Another related concern I have is how the data is refreshed. Would I need to perform a Reload each time I want to analyse the latest set of data? Or, can I just refresh the subset I am analysing.

Thank you very much for your help.

vcoder

[Gysbert_Wassenaar]

Generally, don't load data you know you are not going to use. What's often done is to create a load statement to load all fields, but comment out the fields you don't want to load. You can simply uncomment them later if you find you do need those columns. I would not filter much on values in fields. That's done very easily in the UI. Only if you're certain you don't need records with that data (for example don't load data from 2012 if you only need to analyse 2013).

A reload refreshes all the data. It is possible to do a partial reload, but I don't think this applies to your situation. It's hard to do that dynamically based on the subset you're working with.

[#3]

Thanks very much for your reply, Gysbert.

I've been applying comments to only bring in the fields of interest, but it still takes a very long time to get all the information from the SQL database.

When you say load, are you referring generically to the process or do you mean LOAD? I have been bringing in data from teh DB using a SQL Select statement, then defining the columns of interest separately and further refining using a WHERE date cutoff.

I'm experimenting with the use of a load statement, but still not exactly sure how this can speed things up for me.

If the source data in the SQL table is updated, I assume this is only reflected in my QV table after a reload. Is it psosible for fields to dynamically update, adding or removing only updated records?

[Gysbert_Wassenaar · Accepted solution]

By load I mean the process. The load statement won't speed things up if you need to get data from a database. You'll still need the select statement. Btw, qlikview doesn't parse the select statement, but sends it as is to the database. It just receives the results and puts them in a table in qlikview in-memory associative database. So why use a preceding load when using a select statement? Well, some things are more easily done in qlikview. The preceding load pipes the results from the select to the load where you can do some additional processing. A preceding load is something like:

load *;

select a,b,c from mydb.mytable

where a is not null;

In this case load *; doesn't do anything extra so it's a useless preceding load here.

It's possible to do incremental loading. This works best if in your database your tables contain a field with the modification date(time) of the record. See here for links to posts about incremental reloading.

[#5]

Thank you very much for explaining. I will look into incremental loading as you suggest.

vcoder