2 Replies Latest reply: May 14, 2012 9:13 AM by kfloberg RSS

    Convert SQL-script to a good working QlikView LOAD-statement

      Testfil01

       

      KATEGORI      DATE NAME

           5    20030317 Stina

           5    20060101 Stina

           5    20091201 Stina

           5    20111117 Stina

           5    20111130 Stina

           5    20111219 Stina

           5    20111230 Stina

           7    20090226 Pelle

          12    20110912 Olle

          12    20110927 Olle

       

         

      Testfil02

       

      KATEGORI   FROMDAT SURNAME     

           5    19800101 Nilsson  

           5    19910101 Nilsson  

           5    19920101 Nilsson  

           5    19930101 Nilsson  

           5    19940701 Nilsson  

           5    19950101 Bertilsson

           5    19960101 Nilsson  

           5    19980401 Nilsson  

           5    19980701 Nilsson  

           5    19990101 Nilsson  

           5    20010101 Nilsson  

           5    20020901 Nilsson  

           5    20060101 Karlsson 

           5    20070201 Svensson 

           5    20111225 Rapp     

           7    20110101 Hugosson 

          12    19800101 Martinsson

          12    20110901 Blixt  

       

       

      With the starting point from the two files TESTFIL01 and TESTFIL02 I am trying to create the following new table in a QlikView script but I don’t get the new table the way I want. I am just a beginner…

       

      KATEGORI     DATE   NAME     FROMDAT    SURNAME      

          5      20030317   Stina    20020901   Nilsson   

        5      20060101   Stina    20060101   Karlsson  

        5      20091201   Stina    20070201   Svensson  

        5      20111117   Stina    20070201   Svensson  

        5      20111130   Stina    20070201   Svensson  

        5      20111219   Stina    20070201   Svensson  

        5      20111230   Stina    20111225   Rapp      

        7      20090226   Pelle    - -         

      12      20110912   Olle     20110901   Blixt     

      12      20110927   Olle     20110901   Blixt   

       

      Anyway, I am used to more traditional SQL and the scipt below

      works perfect, but unfortunately not in QlikView.

       

         

      select T01.KATEGORI                            

           , T01.DATE                                

           , T01.NAME                                

           , T02.FROMDAT                             

           , T02.SURNAME                           

        from      TESTFIL01 T01                           

        left join TESTFIL02 T02                      

          on T01.KATEGORI = T02.KATEGORI             

         and T02.FROMDAT                             

            = (select max(T02.FROMDAT)               

                 from TESTFIL02 T02                  

                 where T02.KATEGORI = T01.KATEGORI AND

                       T02.FROMDAT <= T01.DATE)      

      order by T01.KATEGORI, T01.DATE

       

       

       

      What is the best way to ‘convert’ the SQL above into a good working LOAD-statement in QlikView document ?

       

      As you can see I am a ‘QlikView’-beginner…

       

      Thank you in advance!

       

        • Convert SQL-script to a good working QlikView LOAD-statement

          He,

          If you can connect to the database, you can still use your sql code.

           

          Table1:

          Load *;

          sql

          select T01.KATEGORI                           

               , T01.DATE                               

               , T01.NAME                               

               , T02.FROMDAT                            

               , T02.SURNAME                          

            from      TESTFIL01 T01                          

            left join TESTFIL02 T02                     

              on T01.KATEGORI = T02.KATEGORI            

             and T02.FROMDAT                            

                = (select max(T02.FROMDAT)              

                     from TESTFIL02 T02                 

                     where T02.KATEGORI = T01.KATEGORI AND

                           T02.FROMDAT <= T01.DATE)     

          order by T01.KATEGORI, T01.DATE