Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

stevejones1
Not applicable

Incremental Load

Here is my incremental load script , i will loading data on startdate field .

Every time i copy a new file and reload this script my data seems to duplicated.Can some one please check what i am doing wrong below?

//Creating LiveopsTelephonyData qvd

//LiveopsTelephonyData:

//LOAD [Campaign Name],

//     [Start Date],

//     [All Segments],

//     [Answered Segments],

//     [Average Time to Answer (minutes)],

//     [Call Length (minutes)],

//     [Average Call Length (minutes)],

//     [Queue Length (minutes)],

//     [Max Queue Length (minutes)],

//     Abandon,

//     [Abandon %],

//     [Caller Talk Time (minutes)]

//FROM

//$(vQVDPath_QlikMart_QVD)LiveOps.csv

//(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//

//store LiveopsTelephonyData into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

//

//exit script;

//Loading data from qvd

LiveopsTelephonyData:

LOAD [Campaign Name],

    date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD

(qvd);

RecentUpdate:

Load  Max(date([Start Date],'MM/DD/YYYY')) as MaximumDate

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

Let vLastUpdatedDate = Peek('MaximumDate',0,'RecentUpdate');

drop Table LiveopsTelephonyData;

Incremental:

LOAD [Campaign Name],

      date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveOps.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where ([Start Date]) > $(vLastUpdatedDate);

Concatenate

Load

[Campaign Name],

     date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

store Incremental into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

drop Table Incremental;

4 Replies
surendraj
Not applicable

Re: Incremental Load

Just go with this!!

it have detailed explanation!!

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

seems you are not using Where not exists logic to override the updated records..instead of creating a new record each time.

-Surendra

delroekid
Not applicable

Re: Incremental Load

Here is some basic flow for doing incremnetal load....

check if qvd exists

a) if false = do full extract

b) if true = do incremental

1) FULL DATA: load the previous loaded data (full data)

2) FLAG/ID : check the flag/id/date of the last loaded data (peek)

3) OLD PARTIAL DATA: load partial data from full data(1) where data less than the value of peek 

    (drop table in 1)

4) NEW PARTIAL DATA: get new data base on the last loaded data flag/id/date (partial data)

5) concatenate partial data in (4)

     with (1)

6) STORE (5)

shiveshsingh
Not applicable

Re: Incremental Load

Try this once

LiveopsTelephonyData:

LOAD [Campaign Name],

    date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD

(qvd);

RecentUpdate:

Load  Max(date([Start Date],'MM/DD/YYYY')) as MaximumDate

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

Let vLastUpdatedDate = Peek('MaximumDate',0,'RecentUpdate');

drop Table LiveopsTelephonyData;

Incremental:

LOAD [Campaign Name],

      date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveOps.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where date([Start Date],'MM/DD/YYYY') > $(vLastUpdatedDate);

Concatenate

Load

[Campaign Name],

     date([Start Date],'MM/DD/YYYY') as [Start Date],

     [All Segments],

     [Answered Segments],

     [Average Time to Answer (minutes)],

     [Call Length (minutes)],

     [Average Call Length (minutes)],

     [Queue Length (minutes)],

     [Max Queue Length (minutes)],

     Abandon,

     [Abandon %],

     [Caller Talk Time (minutes)]

FROM

$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

store Incremental into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

drop Table Incremental;

Is your data changing on basis of Start Date only?

Here what do you mean by duplicates? you are getting duplicate dates in your final qvd?

Arjunarao
Not applicable

Re: Incremental Load