Incremental data help

[#1]

Hi All,

I am new to Qlikview community, please help me out in getting incremental
data..

First i loaded the data as one cut off date for eg: 30th SEP 2013 and then i
modified the extractor with incremental script ( below script) and scheduled
the extractor to run daily in server and after one month when i cross verfied
the data and found data is not up to date and some data is missing...please
check the below script and advise...

SET vQvdFile='$(vFolderSourceData) XXXXXXX.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

>>> SQL Query Where Clause : $(vIncrementalExpression);

[Table name] :



Load Distinct

column1

coloumn2 ;

SQL select

column1

coloumn2

from table $(vIncrementalExpression)
;

LET vNumRows = NoOfRows(table_name);

TRACE >>> Table
--> XXXX New records loaded with : $(vNumRows);


IF $(vQvdExists) THEN
CONCATEN ATE (table_name)

LOAD * FROM $(vQvdFile) (qvd)

WHERE NOT exists(Party_Site_Id);

END IF

STORE [table_name] into $(vQvdFile)
(qvd); LET
vNumRows = NoOfRows('table_name');

TRACE >>> Table
--> table_name Table loaded with : $(vNumRows);


Drop Table
[table_name];



Regards

Madhu

[Gysbert_Wassenaar]

1. Make sure you use the exact case sensitive table and field names. table_name is not the same as Table name.

2. This clause may cause historical records to be omitted from the qvd load: WHERE NOT exists(Party_Site_Id);

[#3]

Hi Gysbert Wassenaar,

thanks for your reply..

1. we are maintained the table names same with case sensitive..

while copying the code for community we replaced the table name manually so thats the mistake ..

Here iam copying exact code what we used ....

2. i will remove the conditon WHERE NOT exists(Party_Site_Id ); and will try ..

// 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