topic Re: Incremental Load in QlikView

Incremental Load

stevejones1 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Incremental Load

surendraj — Wed, 16 May 2018 20:32:41 GMT

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

Re: Incremental Load

Anonymous — Thu, 17 May 2018 00:17:59 GMT

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)

Re: Incremental Load

shiveshsingh — Thu, 17 May 2018 04:52:46 GMT

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?

Re: Incremental Load

qlikviewwizard — Mon, 21 May 2018 23:47:38 GMT

Hi Try this:

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