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.
-----------------------
-----------------------
--main
SET vLoadMainQVD= 'N';
SET vInsert= 'N';
SET vUpdate= 'N';
SET vInsertUpdate= 'Y';
------------------
------------------
---LoadMainQVD
IF '$(vLoadMainQVD)' = 'Y' THEN
ODBC CONNECT32 TO [scott;DBQ=ZA1 ] (XUserId is WDXfTZNMPDdIHfC, XPassword is HFAIEaFNHLZEHDA);
flights_2009:
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";
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);
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)';
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);
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;
end if;
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.
-----------------------
-----------------------
--main
SET vLoadMainQVD= 'N';
SET vInsert= 'N';
SET vUpdate= 'N';
SET vInsertUpdate= 'Y';
------------------
------------------
---LoadMainQVD
IF '$(vLoadMainQVD)' = 'Y' THEN
ODBC CONNECT32 TO [scott;DBQ=ZA1 ] (XUserId is WDXfTZNMPDdIHfC, XPassword is HFAIEaFNHLZEHDA);
flights_2009:
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";
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);
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)';
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);
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;
end if;
No comments:
Post a Comment