We have different type of loading with Qlikview. Increamental loading is one of them.
For incremental loading the QVD is necessary. Without QVD incremental loading is nothing. First of all the qustion arise why the Incremental Loading is necessary?
So the answer to this question is suppose we have multi million records in the table, and the size of table can grown upto 50GB. So we are not going to load data not from every time from the table. To save time of loading and processing up of data the faster way is QVD. (QVD is 90% faster as from any other source.). So we load all data from table into QVD and we qlikview fetch data from QVD all the time. On other hand, same time the database is growing and our QVD file become outdated and we need to add updated data into the QVD. To perform this we will use INCREMENTAL LOADING. Below i am having the scripts for the incremental loading : 1. insert only 2. update only 3. update and insert both here the most sable technique is insert only. below is the create table script (for oracle): create table flight_data_1 (primary_key number,created_date date ,updated_date date ,Year number ,Month number,Aircraft_Type_ID number,Departures_Scheduled number, Departures_Performed number, Available_Seats number, Transported_Passengers number, Transported_Freight number);
truncate table flight_data_1; drop sequence s2; create sequence s2; select s2.nextval from dual; insert into flight_data_1 values ( 1, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR') ,2009,2,631,35441,32893,2169889,1568806,116024); insert into flight_data_1 values ( 2, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR') ,2009,2,631,35441,32893,2169889,1568806,116024); insert into flight_data_1 values (3, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2009,3,35,1584,15632,82553,22820,574571); insert into flight_data_1 values ( 4, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,2,456,8037,8146,273340,124917,64695); insert into flight_data_1 values ( 5, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,2,817,0,885,84568,53799,94621719); insert into flight_data_1 values ( 6, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,2,612,66780,68379,9265104,6895315,16661790); insert into flight_data_1 values ( 7, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,3,625,3548,3960,394976,322208,101303920); insert into flight_data_1 values ( 8, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,3,691,6964,6968,900,489,440725266); insert into flight_data_1 values ( 9, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,2,614,36858,39681,6281400,4697543,11508820); insert into flight_data_1 values ( 10, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2009,3,627,5542,9441,2504177,1917606,184951562);
--insert ---------------2010\ insert into flight_data_1 values ( 11, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,2,631,35441,32893,2169889,1568806,116024); insert into flight_data_1 values (12, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,3,35,1584,15632,82553,22820,574571); insert into flight_data_1 values ( 13, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,2,456,8037,8146,273340,124917,64695); insert into flight_data_1 values ( 14, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,2,817,0,885,84568,53799,94621719); insert into flight_data_1 values ( 15, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,2,612,66780,68379,9265104,6895315,16661790); insert into flight_data_1 values ( 16, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,3,625,3548,3960,394976,322208,101303920); insert into flight_data_1 values ( 17, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,3,691,6964,6968,900,489,440725266); insert into flight_data_1 values ( 18, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,2,614,36858,39681,6281400,4697543,11508820); insert into flight_data_1 values ( 19, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,3,627,5542,9441,2504177,1917606,184951562); insert into flight_data_1 values ( 20, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2010,3,694,47487,50816,7533855,6181092,7007363);
commit;
----insert/update ---------- select s2.nextval from flight_data_1; update flight_data_1 set DEPARTURES_SCHEDULED = 9191 , updated_date = to_date((sysdate-200)+s2.nextval,'DD-MON-RR') where primary_key = (select max(primary_key) from flight_data_1);
insert into flight_data_1 values ( 21, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,6,631,40093,38756,2546743,2067813,48786); insert into flight_data_1 values ( 22, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,5,636,0,9,63,26,0); insert into flight_data_1 values ( 23, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,4,626,10381,13826,2555544,2060087,296679796); insert into flight_data_1 values ( 24, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,4,614,40138,44213,7006568,5598304,13376786); insert into flight_data_1 values ( 25, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,4,694,46349,49157,7294399,5917732,6501940); insert into flight_data_1 values ( 26, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,4,612,77015,79695,10810602,8346921,18806235); insert into flight_data_1 values (27, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,4,619,32609,32096,4372298,3218725,7872327); insert into flight_data_1 values ( 28, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,5,405,9617,14258,230490,102487,1257004); insert into flight_data_1 values ( 29, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,5,406,0,478,4302,2006,11752); insert into flight_data_1 values ( 30, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'),2011,5,614,40901,44135,6986832,5681758,14001919);
commit;
--update ----------- update flight_data_1 set Departures_Scheduled=7777 , updated_date = to_date((sysdate-200)+s2.nextval,'DD-MON-RR') where created_Date = (select max(created_date) from flight_data_1); insert into flight_data_1 values ( 31, to_date((sysdate-200)+s2.nextval,'DD-MON-RR'),to_date((sysdate-200)+s2.currval,'DD-MON-RR'), 2012,7,622,44864,45432,7437140,6623247,162142678);
commit;
Now open Qlikview - create new document - save it - start copy and paste below scripts tab wise - i have define 5 tabs (main, LoadMainQVD,insert, update, insert_update) - save qvd and reload it. - dont forget to create new ODBC connection.
store flights_2009 into ..\datafiles\base_load_date_basis_qvd.qvd;
MaxDateLoad: LOAD Max(date("CREATED_DATE",'D-MMM-YYYY')) as MaxDate FROM ..\datafiles\base_load_date_basis_qvd.qvd (qvd);
Let MaxID = Null; Let MaxID = peek('MaxDate',0,MaxDateLoad); endif;
---------------------------- ----------------------- ---insert IF '$(vInsert)' = 'Y' THEN
// Get maxdate from table .that will be used for newly added record MaxDateLoad: LOAD Max(date("CREATED_DATE",'D-MMM-YYYY')) as MaxDate FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd);
Let MaxID =null; Let MaxID = peek('MaxDate',0,MaxDateLoad);
ODBC CONNECT32 TO [scott;DBQ=ZA1 ] (XUserId is bOfCeZNMPDdIHTB, XPassword is bLOCHaFNHLZEHAB);
store IncrementalData into [..\datafiles\base_load_date_basis_qvd.qvd];
MaxDateLoad_AfterAppend: LOAD Max(date("CREATED_DATE",'D-MMM-YYYY')) as MaxDate1 FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd);
Let MaxID_AftrAppnd = Null; Let MaxID_AftrAppnd = peek('MaxDate1',0,MaxDateLoad_AfterAppend);
end if;
---------------- -------------------- --update IF '$(vUpdate)' = 'Y' THEN
MaxDateLoad: LOAD Max(date("UPDATED_DATE",'D-MMM-YYYY')) as MaxUpdatedDate, Max(date("CREATED_DATE",'D-MMM-YYYY')) as MaxCreatedDate FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd);
Let MaxUpdatedDate = Null; Let MaxUpdatedDate = peek('MaxUpdatedDate',0,MaxDateLoad);
Let MaxCreatedDate = Null; Let MaxCreatedDate = peek('MaxCreatedDate',0,MaxDateLoad);
ODBC CONNECT32 TO [scott;DBQ=ZA1 ] (XUserId is bOfCeZNMPDdIHTB, XPassword is bLOCHaFNHLZEHAB);
IncrementalData: LOAD "PRIMARY_KEY", "CREATED_DATE", "UPDATED_DATE", YEAR, MONTH, "AIRCRAFT_TYPE_ID", "DEPARTURES_SCHEDULED", "DEPARTURES_PERFORMED", "AVAILABLE_SEATS", "TRANSPORTED_PASSENGERS", "TRANSPORTED_FREIGHT"; SQL SELECT * FROM SCOTT."FLIGHT_DATA_1" where "CREATED_DATE" <= '$(MaxCreatedDate)' and "UPDATED_DATE" > '$(MaxUpdatedDate)' ;
LOAD PRIMARY_KEY, CREATED_DATE, UPDATED_DATE, YEAR, MONTH, AIRCRAFT_TYPE_ID, DEPARTURES_SCHEDULED, DEPARTURES_PERFORMED, AVAILABLE_SEATS, TRANSPORTED_PASSENGERS, TRANSPORTED_FREIGHT FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd) WHERE NOT Exists (PRIMARY_KEY) ;
store IncrementalData into [..\datafiles\base_load_date_basis_qvd.qvd]; end IF;
--------------- --------------- ---insert_update IF '$(vInsertUpdate)' = 'Y' THEN
MaxDateLoad: LOAD Max(date("CREATED_DATE",'D-MMM-YYYY')) as MaxDate, Max(date("UPDATED_DATE",'D-MMM-YYYY')) as MaxUPDate FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd);
Let MaxID = Null; Let MaxID = peek('MaxDate',0,MaxDateLoad);
Let MaxUPdated = Null; Let MaxUPdated = peek('MaxUPDate',0,MaxDateLoad);
ODBC CONNECT32 TO [scott;DBQ=ZA1 ] (XUserId is bOfCeZNMPDdIHTB, XPassword is bLOCHaFNHLZEHAB);
//FETCH NEWLY INSERTED DATA IncrementalData: LOAD "PRIMARY_KEY", "CREATED_DATE", "UPDATED_DATE", YEAR, MONTH, "AIRCRAFT_TYPE_ID", "DEPARTURES_SCHEDULED", "DEPARTURES_PERFORMED", "AVAILABLE_SEATS", "TRANSPORTED_PASSENGERS", "TRANSPORTED_FREIGHT"; SQL SELECT * FROM SCOTT."FLIGHT_DATA_1" where "CREATED_DATE" > '$(MaxID)' or UPDATED_DATE >='$(MaxUPdated)';
Concatenate (IncrementalData) LOAD PRIMARY_KEY, CREATED_DATE, UPDATED_DATE, YEAR, MONTH, AIRCRAFT_TYPE_ID, DEPARTURES_SCHEDULED, DEPARTURES_PERFORMED, AVAILABLE_SEATS, TRANSPORTED_PASSENGERS, TRANSPORTED_FREIGHT FROM [..\datafiles\base_load_date_basis_qvd.qvd] (qvd) WHERE NOT Exists (PRIMARY_KEY) ;
store IncrementalData into ..\datafiles\base_load_date_basis_qvd.qvd;
