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.
Thanks to all your genius!