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.
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.
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.
Date1 & Date2 are from table1, there is only one table in the first SQL script.
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.