2 Replies Latest reply: Dec 16, 2013 10:38 AM by Madhu G RSS

    Incremental data help

      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

        • Re: Incremental data help
          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);

            • Re: Incremental data help

              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