Incremental Load

[stevejones1]

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;

[surendraj]

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

[Anonymous]

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]

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?

[qlikviewwizard]

Hi Try this:

Incremental Load Qlikview – Help Example – Data Analytics and BI WORLD