Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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)
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?