This has been discussed in the forums quite a few. The idea is that the SQL statement is just that: what you send to the driver to retrieve data from a database. The LOAD statement puts what it has been retrieved into QlikView memory. Even when you don't specify it, what QlikView does is a "LOAD *".
The LOAD statement is used in QlikView to do everything you need to do in order to extract, transform and load the data and build your data model at the same time. There are dozens of functions you can use that there are not allowed in SQL. You can create new fields or composite keys in the LOAD statement. You can perform JOINs without troubling the RDBM... As a general rule, the more plain your SQL queries are to your source, the less you impact performance for users.
In other words, let QlikView do the work to build the correct model, transforming data and tables and records when needed, instead of using the transactional to do that, which requires more time and impacts on the transactional performance. In addition, you always rely on a driver that not always allows you to transform as you want, while in QlikView, with the raw data, you can do that.
For me, the benefit is quite clear: build the data model that otherwise I could not. It's always a best practice to use LOAD statements with functions to make the data model just as you need it to be.
Hope that makes sense.
I have read many of the discussion regarding LOAD –v- SQL and it was these discussions that made me revisit what I had done and see if I could improve the applications I already had working.
I also appreciate the point you are making but from my example, something that is very simple and straight forward to do using the SQL commands appears to me to be very clumsy and difficult to do using the LOAD. I just want to have 3 variations of the same date field that I can use for building reports and charts.
I know I could manipulate the date field in the charts but I was under the impression that doing calculations on data in dimensions/expressions in charts impacted performance and doing that work in the load/transform stage was preferable.
Speaking of your example, I think you don't need date#() here. You need it only if the data is not in an explicitly date format, so it tells to read it in date format.
All you need is date() function, e.g.:
date(Create_Date) as Create_Date_AS_IS,
date(Create_Date, 'YYYY-MM') as Create_Date_YYYYMM,
date(Create_Date, 'DD-MM-YYYY') as Create_Date_DDMMYYYY
Create_Date > '2012-01-01 00:00:00';
The AS is a function of QlikView in the example of Michael above. You will be able to use it or not depending on the driver you are using, and the syntax correspoding to your DBM. I may happen that for some reasons, your driver does not recognize the AS in the SELECT statement, so you will have to do it in the LOAD part.
In my case, I always do it in the LOAD part, as it is in QlikView where I can see the names reloaded, what gives me more control on the script and avoids errors i.e.: on capitalization.
Hope that makes sense.