SQL script with where clause

[Anonymous]

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 

[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

[Kushal_Chawda]

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

[Anonymous]

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.

[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)

[Anonymous]

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