5 Replies Latest reply: Sep 26, 2017 6:47 AM by John Jiang RSS

    SQL script with where clause

    John Jiang

      Hi,

       

      I have some very complex SQL statements that would like to convert to Qlikview.

       

      below is one of simplified join tables

       

      FROM table1 t1

      left outer join table2 t2 on t1.field1 = t2.field2

      join table3 t3 on t1.field2 = t3.field2

      join table4 t4 on t1.field3 = t4.field3

      where t1.date between date1 and data2

       

      fortunately, I know the data structure in SQL and a little bit of Qlik behaviours. So, I think I am still ok at this stage with regards to data structure because it only links via table1 fields to others which means a star schema is still in place in Qlik.

       

      left join is ok as Qlik will simply merge two tables into one fact table if I separately load tables into Qlik.

       

      Big question is the where clause in SQL. It extracts data according to the date range in table1 which only should extract approx 10,000 rows when SQL returns. However, table3, table4 are also slow dimensional change data which means they have date range as well not the same to table1. table3 has 2 mil rows and table4 has 4mil rows. When Qlik runs in script, they are in the order of table1, table2, table3, table4.

       

      In SQL, it is not a problem because all table joint returns a total row according to table1 which is approx. 10K rows.

      in Qlik, table1 loaded 10K rows but table3 loaded 2mil rows and table4 loaded 4mil rows which almost kills Qlike document file.

       

      Questions:

      is there a way that can load data to load exact number of rows in table3 and table4 to the same result of SQL execution?

      table1: (10K rows)

      select <all necessary fields> from table1 where date between date1 and data2


      table2: (400 rows, ok)

      select <all necessary fields> from table2

       

      table3: (2mil rows loaded into Qlik, should be 10K  according to table1 where clause)

      select <all necessary fields> from table3

       

      table4: (4milsrows loaded into Qlik, should be 10K  according to table1 where clause)

      select <all necessary fields> from table4

       

      Try to Avoid:

      1. direct copying SQL scripts to Qlik. I think that kills the purpose of dimensional data structure in Qlik. (this would work though)

      2. dumping data to QVD file and do whatever data cleansing there. sth like chopping data fields into separate QVD after loading original using SQL statement.

       

      Thanks to all your genius!

       

      John 

        • Re: SQL script with where clause
          Marcus Malinow

          Hi John,

           

          if you want this to be implemented in SQL, I'd suggest adding to your join conditions for table3 and table4.

           

          for example:

           

          join table3 t3 on t1.field2 = t3.field2 and t3.startdate <= t1.date and t3.enddate >= t1.date

          join table4 t4 on t1.field3 = t4.field3 and t4.startdate <= t1.date and t4.enddate >= t1.date

           

          Marcus

          • Re: SQL script with where clause
            kushal chawda

            where t1.date between date1 and data2.. from which table date1  & data2 are coming?

            • Re: SQL script with where clause
              John Jiang

              Thanks, all. I think I messed up with concepts.


              Marcus,

              I dont want to implement it in SQL. That's why I have this question. I want to know a way around using SQL script to join DB tables without a full load of data for table3 and table4 if possible. However, your suggestion may not be a bad idea as Qlik need me tell it what data to load in select statement, logically. I just wish Qlik has a smarter way translating SQL join tables script with a where clause.

               

              Kushal,

              Date1 & Date2 are from table1, there is only one table in the first SQL script.

               

              hopefully that makes sense.

                • Re: SQL script with where clause
                  Marcus Malinow

                  Ok, in that case you may need to use intervalmatch

                   

                  IntervalMatch and Slowly Changing Dimensions

                   

                  So, for example

                   

                  Table1:

                  LOAD

                      key,

                      date1

                  ;

                  SQL Select

                  key, date1

                  from Table1;

                   

                  LEFT JOIN (Table1)

                  IntervalMatch(date1, key)

                  LOAD

                      startdate,

                      enddate,

                      key

                  ;

                  SQL SELECT

                     startdate,

                     enddate,

                     key

                  FROM table3;

                   

                  LEFT JOIN (Table1)

                  LOAD

                      startdate,

                      enddate,

                      key,

                      [all your other fields]

                  ;

                  SQL

                  SELECT

                      startdate,

                      enddate,

                      key,

                      [all your other fields]

                  FROM Table 3

                  ;

                   

                  (repeat for Table4)

                    • Re: SQL script with where clause
                      John Jiang

                      Hello again Marcus,

                       

                      Yes, how could I forget the interval match?! This was the first lesson I learnt to construct a profit and loss statement for finance. Because PL report has a start date and end date where all transactions fall into that time will be grouped to different accouns.

                       

                      Have to admit that I am not a fan of interval match which is very odd method to me in Qlik. But yes, that's the way to go in Qlik. I think I would happily accept first method you suggested with SQL fix. Easy for maintenance. 

                       

                      Cheers,

                       

                      John