Diff between SQL Select and Load Statement

[#1]

Hi Guys

Can you please let me know what is the difference between a SQL Select .... and Load statement in qlikview script editor?

I used to think, whenever you fetch data from a database you do SQL Select * from table and when you want to get data from a file such as excel or flat file you use Load * from Filename.

Am I right or wrong? or above all am I missing a whole point. Can anyone suggest what's the good practice about them.

Thank you

Kind regards

Sap

[Miguel_Angel_Baeyens]

Hi Sap,

I tend to think of LOAD and SQL as input and output (put and retrieve) functions.

What a LOAD does is put some records into memory to be used further in your charts and so. Actually, when you don't set a LOAD statement but you do a SQL SELECT statement, you are doing an implicit load, meaning you are pulling data from your datasource and pushing into memory.

It's likewise with Excel files. Think of connecting to Excel through and ODBC driver: then you can perform a SQL SELECT statement to get the records you want, and then you can do a LOAD to put those records into memory. Regardless of what your code looks like, the first step is always getting (extracting) some information from a datasource, whatever it is (csv files, data bases, SAP queries, Excel files...), and the second step is to use those records in QlikView, meaning in memory, since QlikView will work with them without being connected to the datasource (you acn load some info into a qvw file and use it later).

But LOAD is used as well to say how do you want those records be dealt, transform or convert them the way you need, create new fields based on those you already have in your datasource, etc.

LOAD put what SQL SELECT gets.

Hope having sehd some light.

[johnw]

I look at it the same as Miguel. You always have a load, whether it's explicit or implied, and I personally ALWAYS write the load explicitly. The load puts data from the source into QlikView. That source can be anything, an inline statement, an SQL SELECT, an Excel file, whatever.

So to me, this:

SELECT *
...

Is more explicitly written like this:

LOAD *;
SQL SELECT *
...

And is functionally equivalent, in the sense of loading data from a source, to this:

LOAD *
INLINE [
...

The load puts what the sql select (or the inline load) gets.

[#4]

Morning Migual and John

Thank you very much Guys. I really do appreciate the effort you have put into explaining me this.

Your posts have been more than helpful.

I think, I have a long way to go before I will be able to master the coding. Probably need a structured course or something.

My work doesn't involve hard coding anymore, but as I am the one who will be developing Qlikview going forward, I have got no choice but to dwell on it. So far the dashboards I have created are getting data from ODBC and some flat files and have very basic coding, because I have done all the hard work of modelling data in SQL server itself (ODBC source), which I reckon can be performed here in Qlikview. What do you think?

Would you guys suggest any extra educational material or anything which I should follow to get the maximum out of this software.

If it helps, I am a systems accountant by profession(Qualified accountant + software engineer) and do lots of freelance MI projects however, this is the first time I am deploying Qlikview. The way I stumble upon Qlikview was, I have delivered my employers a web reporting functionality via SSRS from their diverse data sources however, they wanted more interactive solution and I loaded my data in Qlikview trial software and they liked it a lot and now have been asked to deploy it.

Thanks guys

Sap

[johnw]

If your data is cleanly modeled in SQL server, chances are good that you can read it directly into QlikView, and it will work fine. QlikView might work better with some other data model than the one that was best for your original source, but it's pretty flexible in regards to what it can work with.

Not sure what educational material is good. I started with the tutorial, then we had a several day class, and then it was all on the job learning, trial and error. Then I found the forums and learned a ton that way. I haven't looked, but it wouldn't surprise me if you could learn a lot by looking through some of the example QlikViews and seeing how they do things. You might also download Rob Wunderlich's QlikView Cookbook, which has a number of interesting examples in it.

http://robwunderlich.com/Download.html

[Miguel_Angel_Baeyens]

I agree with John. The Reference Manual is good to start with the syntax and the few examples are fine. The Community is good too (Shares, Community, Wiki).

I must add that I've learned many things using Rob's Cookbook posted by John above.

If you have access to the "Training" section and if not, ask to your QlikView sales partner, there are some webinars and stuff you can find useful too.

[#7]

Hi Rob / Miguel

Thank you very much guys. Much appreciated.

Fingers crossed. I will continue learning from forum and especially you guys or Guru's

Thank you.

Kind regards

Sap

[#8]

...just confirming to the explanations given here.

even I have this doubt, when we use LOAD and when we use SELECT. The scenario I work seems similar to that of Saphelp's ..that is, I use a datamodel created in DB2 as data source. So that means that SELECT statements would suffice my major needs..?

[#9]

Hi Rob/Miguel ...can you kindly confirm if my understanding is right ?

[Miguel_Angel_Baeyens]

Hi,

As stated above, although the LOAD is not mandatory, is highly recommendable. Actually, what QlikView does is to LOAD all records from a SQL SELECT statement without the explicit LOAD:

Companies: // Table Name, not mandatory but highly recommendable as well
SQL SELECT * 
FROM database.owner.Companies; 
// This will do the same
Companies:
LOAD *;
SQL SELECT * 
FROM database.owner.Companies;

It may vary widely depending on your datasource and driver, your database server and manager, but is always useful to create a LOAD will all fields you need, and all formatting required, even if only because some date formatting, number interpretation or simply, conditionals, are not possible nor allowed by the driver. Take into account that QlikView syntaxs is not SQL, although similar.

Companies:
LOAD id AS COMPANY_ID, 
     name as COMPANY_NAME, 
     postcode AS COMPANY_POSTCODE, 
     address AS COMPANY_ADDRESS, 
     If(id > 100, 1, 0) AS FLAG_NATIONAL;
SQL SELECT id, name, postcode, address
FROM database.Companies;

This will first only take the four fields needed for your document, so the database is less stressed, and then is renaming fields or creating new ones based on the exiting or conditionals so you can create associations between tables easier.

In short, I'd say LOAD is always needed and saves development time.

Regards.