3 Replies Latest reply: Sep 18, 2013 4:05 PM by Juan Lopez RSS

    SQL - what type does QlikView use?

    Friedrich Hofmann


      Hi,

       

      I come across different questions rgd. SQL queries once in a while as many of my LOAD statements in QlikView are based on SQL queries.

      My current question is this: I have, in an SQL query, two fields from two different tables with the same fieldname.

      In my SQL query, that is no issue since I precede the name of every field with the tablename. I currently don't in the LOAD_statement, however. How can I avoid having the same fieldname twice in the LOAD_statement?

       

      Therefore I would like to have some material - or an online link - to look up tips and tricks whenever I need some.

      The thing is, there are seemingly endless variations of SQL.

      What type does QlikView use - or what would be the closest to what QlikView uses?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

       

      P.S.: I have the impression I could also do with some tips on improving the performance of a query.

      Generally, is it advisable to do JOINs between two tables in the SQL query or rather in the QlikView LOAD?

        • Re: SQL - what type does QlikView use?
          Israr Khan

          Hi Hofmann.

           

          You can use the preceding load option in QV, check the preceding load check box when selecting table and fields from source table.

          and then you can rename the fields name as:

          Load

          ID,

          Name as FirstName,

          Name as Second Name;

           

          Select * from SQLTable;

           

          if you want to avoid two same fields name from two different table, then you can use AS Keyword to rename fields or Qulaify Keyword, Qualify will put table name with the field name like CUSTOME.Name and SalesPersons.name.

           

          press F1 to see help, and there you can search all qlikview function.

           

          and Qlikview use only three data types (1) Text  (2) Number (3) Date.

           

          Hope its helps.

            • Re: SQL - what type does QlikView use?
              Friedrich Hofmann

              Hi IsrarKhan,

               

              yep, that certainly helps. I have seen that SQL itself also offers some, if more limited, renaming functions. I will combine the two.

              Right now, I seem to have a performance problem: I am loading data from one extremely large database table - well, I don't know, but acc. to the documentation I have, it is the largest and fastest-growing table - and I need to join a few fields from another table.

              I can easily write several tables in the same SQL query, but it takes ages - I don't know if that's because of the table itself or because of my query.

              What would you advise me to do in that case? Should I load both tables individually in SQL and then do the joining in the QlikView LOAD?

               

              Thanks a lot!

              Best regards,

               

              DataNibbler

                • Re: SQL - what type does QlikView use?
                  Juan Lopez

                  Hi Friedrich,

                   

                  Do you REALLY need to join the tables? remember to exploit QV's number one strength, the associative power it offers may save you a couple of joins here and there.

                   

                  if you really need to join them, I would throw that at the server if possible (QV may be built on top of C and C#, but some DBs are built on top of assembler) since they are more accustomed to handling those quantities of data.

                   

                  If throwing it at the server is a "no way", then join them in the script, or try mapping if the join is one field only (you may have to test out, which is the best scenario, if mapping while loading from DB, or when the table is already in memory)

                   

                  some kind of light filter (where clause) could be advisable while on the server in order to pre-filter the great quantity of data, for the join not to take a long time.

                   

                  Remember to double-triple-check if the joins you are making have the fields in common that they are supposed to have (this may be a newbie advice, but happens to everyone once in a while), or else, qlikview will try a natural join between the tables, sticking them together and yielding a m x n records table, which, is not nice,,, for any server!

                   

                  c ya around