Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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!

2 Replies
Not applicable

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

Not applicable

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

Thank You.

The incoming data will be in SKV-files...

I guess this makes my little problem still remaining...  

Community Browser