6 Replies Latest reply: Jan 4, 2013 3:20 PM by Miguel Angel Baeyens de Arce RSS

    Using LOAD AND SQL SELECT

      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.

        • Re: Using LOAD AND SQL SELECT
          Miguel Angel Baeyens de Arce

          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

            • Re: Using LOAD AND SQL SELECT

              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

                • Re: Using LOAD AND SQL SELECT
                  Michael Solomovich

                  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

                    • Re: Using LOAD AND SQL SELECT

                      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.

                  • Re: Using LOAD AND SQL SELECT
                    Marina Alvarez

                    I can't use an as in sql select?

                      • Re: Using LOAD AND SQL SELECT
                        Miguel Angel Baeyens de Arce

                        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