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 ?
