Incremental data help

Mon, 26 Jan 2026 18:19:17 GMT

We have a daily scheduled data extractor to extract data from oracle database with incremental script ( below script), when we cross verified the data after some point of time let’s say 1 month we found that some data is missing it’s not up to date. please check the below script and advise...

// Loading AR Customer Sites
SET vQvdFile='$(vFolderSourceData)CustomerMaster.qvd';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
Set vToday = date(Today(),'DD-MMM-YYYY hh:mi:ss');
Trace >>> Running Customr Sites Scripts;
Trace >>> Running Script at $(vToday);
IF $(vQvdExists) THEN
maxdateTab:
LOAD max(Last_Update_Date) as maxdate
FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = 'WHERE LAST_UPDATE_DATE >= ' & chr(39) & date(peek('maxdate'),'DD-MMM-YYYY') &chr(39) ;
DROP table maxdateTab;
ELSE
LET vIncrementalExpression = '';
END IF
TRACE >>> SQL Query Where Clause : $(vIncrementalExpression);
[AR_CustomerSites] :

Load Distinct

COMPANY as Company,
CUSTOMER_ID as Customer_Id,
CUSTOMER_NUMBER as Customer_Number,
CUSTOMER_NAME as Customer_Name,
PARTY_ID as Party_Id,
PARTY_SITE_ID as Party_Site_Id,
CREATION_DATE as Creation_Date,
CREATED_BY as Created_By,
CUSTOMER_CATEGORY_CODE as Customer_Category_Code,
TRADE_LICENCE_NO as Trade_License_No,
TRADE_LIC_EXP_DATE as Trade_License_Expiry_Date,
SALESREP_ID as Salesrep_Id,
ADDRESS1 as Address1,

ADDRESS2 as Address2,
ADDRESS3 as Address3,
ADDRESS4 as Address4,
CITY as City,

COUNTRY_CODE as Country_Code,
PO_BOX_NUMBER as PO_Box,
SITE_NAME as Site_Name,
SITE_CODE as Site_Code,

SITE_USE_CODE as Site_Use_Code,
SITE_USE_ID as Site_Use_Id,
LOCATION_ID as Location_Id,
LOCATION_CODE as Location_Code,
ORG_ID as Org_Id,
ORIG_SYSTEM_REFERENCE as Org_System_Reference,
CUST_SITE_STATUS as Cust_Site_Status,
CREDIT_LIMIT as Credit_Limit,
TERM_ID as Payment_Term_Id,
PARTY_SITE_NAME as Pasty_Site_Name,
CREDIT_HOLD as Credit_Hold,
CREDIT_CHECK as Credit_Check,
COLLECTOR as Collector,
GL_ID_REC as Code_Combination_Id,
REC_ACCOUNT as GL_Account,
PHONE as Phone,
FAX as Fax,
CONTACT_PERSON as Contact_Person,
CONTACT_PERSON_PHONE as Contact_Person_Phone,
LAST_UPDATE_DATE as Last_Update_Date,
CREDIT_HOLD_STATUS as Credit_Hold_Status ;
SQL select
COMPANY,

CUSTOMER_ID,

CUSTOMER_NUMBER,
CUSTOMER_NAME,
PARTY_ID,
PARTY_SITE_ID,
CREATION_DATE,
CREATED_BY,
CUSTOMER_CATEGORY_CODE,
TRADE_LICENCE_NO,
TRADE_LIC_EXP_DATE,
SALESREP_ID,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
CITY,
COUNTRY_CODE,

PO_BOX_NUMBER,
SITE_NAME,
SITE_CODE,
SITE_USE_CODE,
SITE_USE_ID,
LOCATION_ID,
LOCATION_CODE,
ORG_ID,
ORIG_SYSTEM_REFERENCE,
CUST_SITE_STATUS,
CREDIT_LIMIT,
TERM_ID,
PARTY_SITE_NAME,
CREDIT_HOLD,

CREDIT_CHECK,
COLLECTOR,
GL_ID_REC,
REC_ACCOUNT,

PHONE,
FAX,
CONTACT_PERSON,
CONTACT_PERSON_PHONE,
LAST_UPDATE_DATE,
CREDIT_HOLD_STATUS
from APPS.XXBI_CUST_SITE_DETAILS $(vIncrementalExpression) ;
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites New records loaded with : $(vNumRows);
IF $(vQvdExists) THEN
CONCATENATE (AR_CustomerSites)
LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists(Party_Site_Id);
END IF

STORE [AR_CustomerSites] into $(vQvdFile) (qvd);
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites Table loaded with : $(vNumRows);
Drop Table [AR_CustomerSites];

rergards

Madhu

Re: Incremental data help

Tyler_Waterfall — Wed, 05 Feb 2014 22:11:39 GMT

Madhu G,

From your script I would assume that the data gets updated daily and is unique by day, meaning that once the database is updated for a given day, the data for that day is never updated. Is that the case?

I ask because we saw issues with this type of date-driven incremental loading because the database was updating older records (than the ones that were updated since our last reload). Records were being modified, reinstated, or removed.

Re: Incremental data help

rwunderlich — Wed, 05 Feb 2014 22:57:43 GMT

It looks ok because you are testing LAST_UPDATE_DATE which I assume gets updated when an older record gets updated.

Is PARTY_SITE_ID a true primary key? That is, is it unique for every record?

-Rob

topic Re: Incremental data help in QlikView

Incremental data help

Re: Incremental data help

Re: Incremental data help