Using LOAD AND SQL SELECT

[#1]

I am trying to understand the differences and benefits when using LOAD before  SQL SELECT statement.

I have been using the following SQL SELECT statement to load data from a MS SQL Table.

Customers:
SQL SELECT

    SUBSTRING(Description, 6, 7)     AS CUST_ID,

    Create_Date       AS CUST_DateCreated,

    SUBSTRING(CONVERT(VARCHAR(11), Create_Date, 113), 4, 3)  AS CUST_CreatedMMM,

    CONVERT(VARCHAR(7), Create_Date, 120)     AS CUST_CreatedYYYYMM,
   
    CONVERT(VARCHAR(10), Create_Date, 103)    AS CUST_CreatedDDMMYYYY

FROM
     CustomerTable
WHERE
     Create_Date > '2012-01-01 00:00:00';

Create_Date is in "dd/mm/yyyy hh:mm:ss" format

I need to date convertions to help with the charts and analysis and using just the SQL SELECT statement this works very well.

However, I've been trying to do the same thing using a LOAD statement on its own and with using LOAD and SQL SELECT together.

The Syntax using a LOAD statement doesn't give me the same ability to convert date formats that the SQL statement does. I tried but cannot get the date converted from dd/mm/yyyy hh:mm:ss to YYYY-MM AND DD-MM-YYYYY....I need both.

I tried using the DATE(Date#(Create_date,'YYYY-MM')) option but this just gives me blank values

I tried using the string oprerators to split the date up and piece it back together again but that didn't work either

So how would I get the same fields using LOAD and why would I use LOAD when using SQL SELECT makes this so much easier.

I have a feeling I'm missing something really obvious here or just not understanding what benefit LOAD provides.

[Miguel_Angel_Baeyens]

Hi Mark,

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.

Check this post and this other post about previous discussions about LOAD and SQL.

Hope that makes sense.

Miguel

[#3]

Miguel

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.

Mark

[Anonymous · Accepted solution]

Mark,

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

LOAD
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
;
SQL SELECT
   Create_Date
FROM
     CustomerTable
WHERE
     Create_Date > '2012-01-01 00:00:00';

Regards,
Michael

[#5]

Michael

Thanks this looks like it gives me what I will need...I was trying to use the Date(Date#()) as I couldn’t get the string functions (e.g subfield, left, mid) to give me what I wanted to work so I was hoping this would convert the date it if it was in some odd format.

Thanks for the help.

[farolito20]

I can't use an as in sql select?

[Miguel_Angel_Baeyens]

Hi,

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.

Miguel