11 Replies Latest reply: Jul 14, 2011 10:44 PM by Rajeev Sontakey RSS

    General Script Error while Storing QVD

      I encounter a "General Script Error' while storing a QVD whose data is fetched from a Database. I get this frequently. What could be the problem ?

      I have understood that it could be a memory issue while writing to the disk (i/o operation). The code is attached.

      Any suggesstions ?

       

       

      6/27/2011 12:55:00 AM: 0828  LOAD
      6/27/2011 12:55:00 AM: 0829  PROPOSALID,
      6/27/2011 12:55:00 AM: 0830  if(PROPOSALID=AGREEMENTID_UTLCPF or AGREEMENTNO=TRANSFER_CEF_TEF_AGREEMENTNO,'17',if(AGREEMENTNO='UTL018066O0900121713','31',SCHEMEID)) as SCHEMEID,
      6/27/2011 12:55:00 AM: 0831  if(PROPOSALID=AGREEMENTID_UTLCPF or AGREEMENTNO=TRANSFER_CEF_TEF_AGREEMENTNO,'CV',if(AGREEMENTNO='UTL018066O0900121713','FASEQ',PRODUCTFLAG)) as PRODUCTFLAG,
      6/27/2011 12:55:00 AM: 0832  if(AGREEMENTNO=AGREEMENTNO_NEW,NEW_BRANCHID,
      6/27/2011 12:55:00 AM: 0833  if(num(trim(LESSEEID))=num(trim(CUSTOMERID_DLFIN)),if(isnull(BRANCHID_DLFIN),BRANCHID,BRANCHID_DLFIN),BRANCHID)) as BRANCHID
      6/27/2011 12:55:00 AM: 0834  resident LEA_AGREEMENT_DTL_TEMP
      6/27/2011 12:55:00 AM:           4 fields found: PROPOSALID, SCHEMEID, PRODUCTFLAG, BRANCHID, 411,482 lines fetched
      6/27/2011 12:55:10 AM:       Joining/Keeping
      6/27/2011 12:55:14 AM: 0835  drop table LEA_AGREEMENT_DTL_TEMP
      6/27/2011 12:55:16 AM: 0836  Store LEA_AGREEMENT_DTL into D:\Qlik2Finance\Data\RawQVD\LEA_AGREEMENT_DTL.qvd
      6/27/2011 12:55:18 AM:       General Script Error
      6/27/2011 12:55:19 AM:       Execution Failed
      6/27/2011 12:55:19 AM:      Execution finished.
      
        • General Script Error while Storing QVD
          Miguel Angel Baeyens de Arce

          Hello Sajeev,

           

          Try deleting and letting the script creating the file again. That sometimes happen when you have some antivirus software or if some previous load crashed, and the operating system keeps the file lock as open.

           

          Probably obvious but make sure that the user running the QlikView processes have permissions enough to write/modify files.

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

          • General Script Error while Storing QVD

            I use ADMIN account on the server and I have all the rights to write/modify files. I tried the suggesion give by Mr.Miguel Angel Baeyens. It worked that time but after second reload it gave the same error.

             

            Few notes -

            1. The fetching consist of 330 fields to load from database. Too many fields to load. Could this be a problem ?

            2. The server I use is Windows Server 2003 Enterprise x64 with 8 CPUs of 2.66 GHz. 64 GB RAM.

            3. Only this QVD creates a problem. All other QVDs are stored sucessfully.

              • General Script Error while Storing QVD
                Kaushik Solanki

                Hi,

                 

                   I feel the no of fields is a problem.

                 

                   Try storing less no of fields.

                 

                Regards,

                Kaushik Solanki

                • Re: General Script Error while Storing QVD
                  Miguel Angel Baeyens de Arce

                  Hello Rajeev,

                   

                  Although the number of fields is high, there are no known limitations (at least, none that I'm aware of) in the number of fields a QVD can store. Some different is how this QVD will perform.

                   

                  In regards to the hardware, again, it shouldn't be a problem. Obviously, the better hardware the better performance you will get.

                   

                  Anyway, just after the STORE instruction in your script, set a

                   

                  SLEEP 5000; 
                  

                   

                  That means that the execution of the script will pause for 5 seconds. Try modifying the value after sleep just in case the operating system is not able to store that file properly.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

                    • Re: General Script Error while Storing QVD
                      Miguel Angel Baeyens de Arce

                      Rajeev,

                       

                      Apart from the above, I see in your logs that you are joining/keeping some tables then dropping and then storing. Since QlikView concatentates implicitly two tables provided they have the same number and name of fields, check that part of the script (or share it here is possible) so the drop part doesn't affect the creation of the table.

                       

                      Regards.

                       

                      Miguel Angel Baeyens

                      BI Consultant

                      Comex Grupo Ibérica

                        • Re: General Script Error while Storing QVD

                          The complete LOAD Script is

                           

                          LEA_AGREEMENT_DTL_TEMP:
                          LOAD AGREEMENTNO,
                              PROPOSALID,
                              LESSEEID,
                              SCHEMEID,
                              PRODUCTFLAG,
                              num(BRANCHID) as BRANCHID
                          FROM D:\Qlik2Finance\Data\RawQVD\LEA_AGREEMENT_DTL.qvd (qvd);
                          left join
                          LOAD AGREEMENTNO,
                              AGREEMENTNO_NEW,
                              NEW_BRANCHID
                          FROM D:\Qlik2Finance\Data\InteremdiateQVD\AGRMT_BRANCH_RFG.qvd (qvd);
                          left join
                          LOAD CUSTOMERID as LESSEEID,
                               CUSTOMERID as CUSTOMERID_DLFIN,
                               BRANCHID_DLFIN
                          FROM D:\Qlik2Finance\Data\InteremdiateQVD\DLFIN_CUSTOMER_BRANCH.qvd (qvd);
                          left join
                          LOAD AGREEMENTID as PROPOSALID,
                          AGREEMENTID as AGREEMENTID_UTLCPF
                          FROM D:\Qlik2Finance\Data\InteremdiateQVD\UTLCPF_SCHEME_PRODUCT.qvd (qvd);
                          left join
                          LOAD AGREEMENTNO, 
                          AGREEMENTNO as TRANSFER_CEF_TEF_AGREEMENTNO
                          FROM D:\Qlik2Finance\Data\InteremdiateQVD\TRANSFER_FROM_CEF_TO_TEF.qvd (qvd);
                          
                          
                          LEA_AGREEMENT_DTL:
                          LOAD PROPOSALID,
                              AGREEMENTID,
                              AGREEMENTTYPE,
                              AGREEMENTNO,
                              APPLID,
                              ASSETCOST,
                              AMTFIN,
                              INTRATE,
                              EFFRATE,
                              MARKUP,
                              INTTYPE,
                              TENURE,
                              FREQ,
                              NUMINSTL,
                              INSTALTYPE,
                              INSTLMODE,
                              ADVANCEINSTL,
                              PRETAXIRR,
                              POSTTAXIRR,
                              SANCTIONDATE,
                              AUTHID,
                              STATEID,
                              CATGID,
                              TAXRATE,
                              BOUNCECHEQUEFEE,
                              BROKERAGERATE,
                              MGMTFEERATE,
                              RVRATE,
                              SDRATE,
                              OVERDUERATE,
                              AGREEMENTDATE,
                              COMMENCEDATE,
                              CLOSUREDATE,
                              RESCHEDULEDATE,
                              STATUS,
                              SPOTINTRATE,
                              SCHEMEID as OLD_SCHEMEID,
                              MARGINMONEY,
                              PROPOSALCRDATE,
                              PROPOSALCRBY,
                              AGREEMENTCRDATE,
                              AGREEMENTCRBY,
                              PROPOSALMODDATE,
                              PROPOSALMODBY,
                              CREDITAUTHDATE,
                              CREDITAUTHBY,
                              PRDDOCAUTHDATE,
                              PRDDOCAUTHBY,
                              PODDOCAUTHDATE,
                              PODDOCAUTHBY,
                              CREDITAUTHREQD,
                              PRDAUTHREQD,
                              PODAUTHREQD,
                              LESSEEID,
                              ACTION,
                              ACTIONDATE,
                              PRINT_FLAG,
                              DUEDAY,
                              CREDITAUTHREQD_S,
                              CREDITAUTHDATE_S,
                              CREDITAUTHBY_S,
                              CREDITAUTHREQD_O,
                              CREDITAUTHDATE_O,
                              CREDITAUTHBY_O,
                              AMTOTHERS,
                              REBATE,
                              PENALTY,
                              ACCRUAL_FLAG,
                              CURRENCYID,
                              PRININTCAL,
                              DEANNUALIZE,
                              APPDATE,
                              EMI,
                              RESVALUE,
                              ACCESSAMT,
                              PREPAYPENALTY,
                              MARGINRATE,
                              FINALSOURCE,
                              FINALSOURCEID,
                              FIRSTSOURCE,
                              FIRSTSOURCEID,
                              PROMOTIONSCHEME,
                              LOAN_IN_NAME,
                              SUPPLIERID,
                              DPERYR,
                              DISBURSALDATE,
                              FORECLOSELOCKLN,
                              PRODUCTFLAG as OLD_PRODUCTFLAG,
                              PDCFLAG,
                              DECIMALS,
                              RS_CONFIRM,
                              GRACEPERIOD,
                              LEA_LAST_ACR_DATE,
                              LEA_NON_ACR_FLAG,
                              LEA_ACR_INT_AMT,
                              BATCHNUMBER,
                              ACCOUNTING_MONTHLY_IRR,
                              LEA_MONTHLY_IRR,
                              MARGIN_MONEY_2,
                              NOTEPAD,
                              VAT_PRIN_RATE,
                              VAT_INT_RATE,
                              CRSCORE,
                              AGE,
                              MATURITY_AGE,
                              NPA_STAGEID,
                              BILL_FLAG,
                              LAST_LPI_DATE,
                              NPA_DATE,
                              DPD,
                              DPD_STRING,
                              DATELASTUPDT,
                              DISBURSEDAMOUNT,
                              FINANCECHARGE,
                              BENEFITAMOUNT,
                              DISBURSALTO,
                              MKTGID,
                              BROKERID,
                              REFAGREEMENTID,
                              ADVDISBINTRATE,
                              VAT_INSTL_RATE,
                              VAT_INPUT,
                              DPD_STRING_STAMPDATE,
                              BUCKET,
                              CURRSEQNO,
                              GRACE_INTEREST_CHARGE,
                              GRACE_ADJUST_SCHEDULE,
                              SANCTIONID,
                              AUTCLOS_FLAG,
                              MANUAL_NPA_MOVEMENT_FLAG,
                              DISB_TYPE,
                              NO_DISBURSAL,
                              PEMI_RATE,
                              INTTAXRATE,
                              TDSRATE,
                              LPIRATE,
                              FLOATINGFLG,
                              PLRTYPE,
                              TAXAPPLICABLE,
                              BALANCE_TRANSFER,
                              RELATIONAL_DISCOUNT,
                              OTHER_DISCOUNT,
                              CO_APPL_REQ,
                              GUARANTOR_REQ,
                              SEC_DEPOSIT_REQ,
                              FORECLOSE_ALLOWED,
                              FORECLOSE_PENALTY,
                              FORECLOSE_LOCK_PERIOD,
                              ATMCODE,
                              RATE,
                              FEERATE,
                              COSTOFFUNDS,
                              LOSS,
                              EXPENSES,
                              CUSTOMERYIELD,
                              PRETAXROI,
                              ROI,
                              TAX,
                              TAX_PERCENTAGE,
                              SECURITYCOVER,
                              SECURITYTAKEN,
                              CROSS_COLLATERAL,
                              BILLNO,
                              RELATIONSHIP_DISC,
                              PROFESSIONAL_DISC,
                              PREPAY_ALLOWED,
                              PREPAY_PENALTY,
                              PREPAY_LOCK_PERIOD,
                              FORECLOSURE_PENALTY,
                              DSSNO,
                              MATURITYDATE,
                              DEL_MONTHLY_STRING,
                              MATURITYAMT,
                              TENURETYPE,
                              LPITYPE,
                              ACTUAL_MONTHS,
                              ACTUAL_DAYS,
                              MATCH_FOUND,
                              DEDUPE_RESULT,
                              EMPLOYERID,
                              REPAYMENT_EFFECTIVE_DATE,
                              OUTSTANDINGPRINPRECENT,
                              MIN_PENALAMOUNT,
                              EMP_FLAG,
                              PLRRATE,
                              INSURCOID,
                              INSUR_PRODUCTID,
                              INSURANCE_FLAG,
                              INSURED_TILL,
                              DEPOSIT_ACCOUNT,
                              SPREAD2,
                              FLOATING_FREQUENCY,
                              LAST_FLOAT_SPREAD_DATE,
                              LTV_MULTIPLIER,
                              DISBURSEMENT_DIRECT_CREDIT,
                              STAT_ACC_FREQUENCY,
                              SYSTEM,
                              AMTFIN_REGULAR,
                              AMTFIN_BONUS,
                              FREQ_BONUS,
                              INSTLMODE_BONUS,
                              ADVANCEINSTL_BONUS,
                              DISBURSALDATE_BONUS,
                              INT_ROUNDOFF_PARA,
                              INT_ROUNDTILL,
                              INSTLAMT_ROUNDOFF_PARA,
                              INSTLAMT_ROUNDTILL,
                              PRIN_BASE_UNIT,
                              FIXED_TERM,
                              INTSTART_DATE_BONUS,
                              INSTALTYPE_BONUS,
                              INS_BILL_FLAG,
                              SPECIAL_SPREAD,
                              EMI_BONUS,
                              NEXT_FLOAT_PRIME_DATE,
                              NEXT_FLOAT_SPREAD_DATE,
                              REFINANCE_RECEIPTID,
                              STEP_RATE,
                              AGE_PAST_AGR,
                              INVOICE_FLAG,
                              RC_FLAG,
                              SERVICETAXRATE,
                              DME_ID,
                              GROUPID,
                              PROMOTIONID,
                              FILENO,
                              APP_FORMNO,
                              HFI_FLAG,
                              HFI,
                              EMPLOYER_ADDRESSID,
                              EMPCODE,
                              SPREADID,
                              INITIAL_EFFRATE,
                              INTSTART_DATE_REGULAR,
                              TRANCHING_FLAG,
                              TRANCHING_TYPE,
                              COMPOUND_FREQ,
                              INT_REBATE_FLAG,
                              ACTUALIRR,
                              DISB_STATUS,
                              GROSS_LTV,
                              NET_LTV,
                              AMT_REQUESTED,
                              TENURE_REQUESTED,
                              RATE_EMI_FLAG,
                              ASSET_MARKED,
                              DISBURSALAMOUNT,
                              ORG_SCHEMEID,
                              LSI_AGREEMENT_NO,
                              CHRG_FLG,
                              DISB_FLAG,
                              HL_FILE_NO,
                              OLD_AGREEMENTNO,
                              PEMI_BILLING,
                              REPAY_FLG,
                              TEMPUPD_DUEDATE,
                              MIN_FLOAT_RATE,
                              PUTCALL_FLAG,
                              BILLTYPE,
                              HUNDINO,
                              HUNDIDATE,
                              HUNDIDUEDATE,
                              HUNDIAMT,
                              STAMPVALUE,
                              STAMPRATE,
                              INTTAX,
                              PROGRAM,
                              DIVISION,
                              MARGINDAYS,
                              REMARKS,
                              TDS,
                              PARTYID,
                              CUMULATIVE_INTEREST,
                              FINANCIAL_QUANTUM,
                              CPF_FIRST_CHARGE_FLAG,
                              CPF_ARPS_NUMBER,
                              CPF_APP_AMOUNT,
                              CPF_MAXIMUM_MONTHLY,
                              DEALER_PARTICIPATION_RATE,
                              OVERDUE_PLRTYPE,
                              OVERDUE_MARKUP,
                              APP_LOAN_LIMIT,
                              PSB_APPL_NO,
                              SUB_ACCT,
                              COLLATERAL_ACCT,
                              FLAT_SPREAD,
                              GRACE_DAYS,
                              GRACE_AMT,
                              PREV_LOAN_NO,
                              LINECODEID,
                              SECURITY_CODE,
                              INSTL_FLAG,
                              ADMINFEE_CALC_FLAG,
                              LPP_STOP_FLAG,
                              DISBURSEMENT_STOP_FLAG,
                              GST_RATE,
                              GST_CONSTANT,
                              RULE_OF_78,
                              CPF_INITIAL_WITHDRAWN,
                              REVOLVING_FLAG,
                              REDEMPTION_FLAG,
                              INTEREST_UPFRONT_FLAG,
                              INTEREST_UPFRONT_AMT,
                              INTEREST_STARTMONTH,
                              REPAYMENT_STARTDATE,
                              TAX_STOP,
                              BRANCHID as OLD_BRANCHID,
                              NPA_PRO_FLAG,
                              NPA_PRO_REMARKS,
                              FINAL_NPA_STAGEID,
                              NPA_MANUAL_FLAG,
                              NPA_REASON,
                              NPA_STAMP_DATE,
                              REREGULAR_DATE,
                              RESCHEDULE_STOP,
                              NPA_MANUAL_REMARKS,
                              FT_BATCHNUMBER,
                              LOS_AGREEMENT,
                              DIFF_DAYS_METHOD,
                              VAT_APPLICABLE,
                              MIN_REPAY_AMT,
                              CAPITALIZE_INTEREST,
                              FAS_ACCNO_SEQ,
                              SECONDARY_PARTY_TYPE,
                              FACILITYID,
                              CUSTOMER_OPDIV,
                              PTP_TYPE,
                              PTP_UNIT,
                              NO_OF_BILLS,
                              ACCOUNT_OFFICER
                          FROM D:\Qlik2Finance\Data\RawQVD\LEA_AGREEMENT_DTL.qvd (qvd);
                          left join
                          load
                          PROPOSALID,
                          if(PROPOSALID=AGREEMENTID_UTLCPF or AGREEMENTNO=TRANSFER_CEF_TEF_AGREEMENTNO,'17',if(AGREEMENTNO='UTL018066O0900121713','31',SCHEMEID)) as SCHEMEID,
                          if(PROPOSALID=AGREEMENTID_UTLCPF or AGREEMENTNO=TRANSFER_CEF_TEF_AGREEMENTNO,'CV',if(AGREEMENTNO='UTL018066O0900121713','FASEQ',PRODUCTFLAG)) as PRODUCTFLAG,
                          if(AGREEMENTNO=AGREEMENTNO_NEW,NEW_BRANCHID,
                          if(num(trim(LESSEEID))=num(trim(CUSTOMERID_DLFIN)),if(isnull(BRANCHID_DLFIN),BRANCHID,BRANCHID_DLFIN),BRANCHID)) as BRANCHID
                          resident LEA_AGREEMENT_DTL_TEMP;
                          drop table LEA_AGREEMENT_DTL_TEMP;
                          Store LEA_AGREEMENT_DTL into D:\Qlik2Finance\Data\RawQVD\LEA_AGREEMENT_DTL.qvd;
                          
                          drop table LEA_AGREEMENT_DTL;
                          
                        • Re: General Script Error while Storing QVD

                          Adding sleep after store command is working..

                      • Re: General Script Error while Storing QVD

                        Hi,

                         

                        I'm also encountering exactly same issue though frequency is very random, a couple of times per day or never occured and problematic QVD file also changes. I had never seen this issue before late June and my local support contact has a bit doubts if Windows hotfixes/security patch or unti-virus software locking QVD file from being written.

                         

                        Reload frequency: every 1 mins

                        Reload engine: QVDistributionService.exe

                        Num of QVDs written by reload: 4

                         

                        OS: Windows server 2008 R2

                        Unti-virus software: Symantec Endpoint Protection 11.0.5002.333

                        Hotfixes/security patches as following:

                        W2008KBs.JPG