topic Re: How to use incremental load for insert ?? in QlikView

How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 06:49:26 GMT

Hey ,

Ia have loaded a excel sheet data and created a qvd also.

Now i want to use incremental load for the data that i inserted now.

Should i just copy and paste below code or i need to define modified date and last execute data nd all. ??

Please help...

SQL SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM Disaster

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM Disaster1.QVD

WHERE NOT Exists(Id);

STORE Disaster INTO Disaster1.QVD;

Thanx.

Re: How to use incremental load for insert ??

avinashelite — Fri, 05 Sep 2014 06:54:30 GMT

Hi Nikhil,

Refer this PPT.

Re: How to use incremental load for insert ??

Fri, 05 Sep 2014 06:54:49 GMT

u should get this date from QVD (max exec date or max created date) "LastExecTime",

which can u compare with the query as u did.

it will work fine!!!

Re: How to use incremental load for insert ??

Gysbert_Wassenaar — Fri, 05 Sep 2014 06:56:20 GMT

It looks reasonable if you want to insert and update incrementally. If you create the variable LastExecTime correctly in the date format that your SQL database server expects then it should work. The new and the updated records are retrieved from the SQL source and concatenated with the historical records that were not updated.

Re: How to use incremental load for insert ??

Fri, 05 Sep 2014 06:56:57 GMT

u can follow this blog post

Knowledge is Here..: QV5 Incremental loading

Re: How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 07:03:11 GMT

Hey,

Here i am using dummy data . I have entered some values in excel shet and name it : DIsaster.xlxs. It is saved on desktop.

Now i created qvd as : Disaster1.qvd and insert dummy record.

Now i want to use incremental load for updating data. I am using following code but it is generating error.

Please help :

Directory;

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

[Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

STORE Disaster into C:\Users\nikhil.garg\Desktop\disaster1.qvd

SQL SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM [Copy of Disaster.xlsx]

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM Disaster1.QVD

WHERE NOT Exists[Id];

STORE Disaster INTO Disaster1.QVD;

Re: How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 07:25:02 GMT

Can you please tell me in detail from where to get modified date and execute time ??

thanx

Re: How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 08:54:30 GMT

HEy please solve my this issue .

I am having problem in incremental load.

I am using excel sheet dummy data stored on desktop. I created qvd file. Now i updated data and want incremental load. I have used following code. Please help me what to do :

Directory;

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

[Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

STORE Disaster into C:\Users\nikhil.garg\Desktop\disaster1.qvd ;

// Incremental load :

SQL SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM 'C:\Users\nikhil.garg\Desktop\Disaster_Data\[Copy of Disaster.xlsx]'

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM C:\Users\nikhil.garg\Desktop\disaster1.qvd

WHERE NOT Exists[Id];

STORE Disaster INTO C:\Users\nikhil.garg\Desktop\disaster1.qvd;

Re: How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 10:30:14 GMT

HEy lastExecution time variable is predefined. But to compare it modification time of table , how would i gte modification time ??

Re: How to use incremental load for insert ??

Fri, 05 Sep 2014 11:15:25 GMT

sry for late reply

***j4info****blogspot****in /2014/08/ qv5-incremental-loading.html

check above link and remove space before using above link from "08/ qv5 "

Re: How to use incremental load for insert ??

Fri, 05 Sep 2014 11:17:24 GMT

QV5 Incremental loading

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;

Re: How to use incremental load for insert ??

Fri, 05 Sep 2014 11:18:12 GMT

or you can so google search

qv5-incremental-loading +j4info

Re: How to use incremental load for insert ??

avinashelite — Fri, 05 Sep 2014 11:43:03 GMT

Hi Nikhil,

Can you please post your app and data. we will help it out.

Re: How to use incremental load for insert ??

nikhilgarg — Fri, 05 Sep 2014 11:47:41 GMT

Hey,

If i am not wrong , in your this example , you have assumed a field with date to compare it with last execution date.

But if i donot have any date file or any create date filed then how does i compare with last execute time ??

Please help.

THanx