5 Replies Latest reply: Feb 6, 2015 3:34 PM by Curt Daughtry RSS

    Creating and Dropping temp tables

    John mingus
      Hello, Qlikview group. I have been an acive reader of this community but just signed up as I am hoping for help with SQL to Qlikview script. I have taken a developer/designer class several months ago but until now have not applied the knowledge until now...
      Question:
           I wrote a SQL query and tried to copy and paste this into Qlikview but I am getting an error. Can someone take a look at  the script and tell me where I went wrong and maybe give me some pointer for the future, thanks...
      Hopefully I am somewhat close
      ODBC
      CONNECT32 TO xxxxxx
      SQL
      TEMPTEST:
      USE AA
      SELECT TOP 1000
      [AA]
             ,[mktId]
                 ,[effDt]
             ,[termDt]
             ,[termReasonCd]
             ,[uniqueNbr]   
            
      FROM [Ntwk];
        
      SQL
      TEMPTEST2:
      USE AA
      SELECT TOP 1000      
      [AA],
      [BB]

                    
      FROM [NtwkMst] ;

      SQL
      USE AA
      SELECT TOP 1000      [effDt]
             ,[termDt]
             ,[termReasonCd]
             ,[AA]
      ,[BB]
            
         FROM TEMPTEST2
      INNER JOIN TEMPTEST ON TEMPTEST2.AA = TEMPTEST.AA;

      DROP TABLE TEMPTEST;
      DROP TABLE TEMPTEST2
        • Re: Creating and Dropping temp tables
          Massimo Grossi

          and the error is?

           

          try this (remove use..., if needed put the db before the table, move tempest)

          and let we know

           

           

          ODBC CONNECT32 TO xxxxxx;


          TEMPTEST:

          SQL
          SELECT TOP 1000

          [AA]
                 ,[mktId]
                     ,[effDt]
                 ,[termDt]
                 ,[termReasonCd]
                 ,[uniqueNbr]   
          FROM [Ntwk];

          • Re: Creating and Dropping temp tables
            Curt Daughtry

            modify your script to look as written below

             

            ODBC CONNECT32 TO xxxxxx

            TEMPTEST:

            SQL

            //USE AA

            SELECT TOP 1000

                   [AA]

                  ,[mktId]

                  ,[effDt]

                  ,[termDt]

                  ,[termReasonCd]

                  ,[uniqueNbr]

            FROM [Ntwk];

             

             

            inner join(TEMPTEST)

            TEMPTEST2:

            SQL

            //USE AA

            SELECT TOP 1000

              [AA],

              [BB]

            FROM [NtwkMst];

             

            //DROP TABLE TEMPTEST;

            //DROP TABLE TEMPTEST2;

            DROP FIELDS [mktId], [uniqueNbr];

              • Re: Creating and Dropping temp tables
                Curt Daughtry

                My previous comment is the most straight-forward solution based on what you provided...however, below will provide some explanation for how to perform the load from temp table approach you originally outlined using qlikview.

                 

                You were definitely in the right direction from a logic perspective....just a few areas related to syntax were off.

                 

                If you load data in qlikview using the LOAD or SQL Select statements the table resides in the qlikview app memory. To access the table later in the script (for any number of potential reasons), you must use

                LOAD...RESIDENT <tablename>;

                 

                Here's an example.

                 

                 

                 

                 

                // use load statement to create temp table 1 from source qlikview data file (i.e. qvd)

                TempTable1:

                LOAD

                  field1,

                  field2,

                  field3

                from source.qvd(qvd);

                 

                 

                // use sql select statement to create temp table 2 and then

                // left join to temp table 1 on TempTable1.field1 = TempTable2.sql_field1 by aliasing the field names to be the same

                left join (TempTable1)

                TempTable2:

                SQL Select

                  sql_field1 as field1,

                  sql_field2,

                  sql_field3

                from dbo.TempTable;

                 

                 

                 

                 

                // use load..resident statement to reload temp table 1 as a new table called Table1

                // also alias field name to illustrate simple example of reloading an existing "temp" table

                Table1:

                LOAD

                  field1 as Field1,

                  field2 as Field2,

                  field3 as Field3,

                  sql_field2 as SQL_Field2,

                  sql_field3 as SQL_Field3

                RESIDENT

                TempTable1;

                 

                 

                // remove temp table 1 from qlikview memory as it is no longer needed

                drop table TempTable1;

              • Re: Creating and Dropping temp tables
                John mingus


                @autopilot - Your solution worked, Now it's about understamding the difference between Qlikview and SQl syntax. Thanks!     

                  • Re: Creating and Dropping temp tables
                    Curt Daughtry

                    I wanted to provide the solution and then take time to write the explanation....which I added in a reply a minute ago.

                     

                    Let me know if that doesn't make sense. For whatever reason I can't seem to get the community editor to paste the text with the colors for load script so you may want to copy paste into a qv app for better readability.