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

Mon, 14 May 2012 12:20:19 GMT

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

Mon, 14 May 2012 13:01:02 GMT

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

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

Mon, 14 May 2012 13:13:59 GMT

Thank You.

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

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

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

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

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

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