Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental data help

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

2 Replies
Tyler_Waterfall
Employee
Employee

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.

rwunderlich
Luminary Alumni
Luminary Alumni

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