topic Re: General Script Error while Storing QVD in QlikView

General Script Error while Storing QVD

Mon, 27 Jun 2011 07:36:00 GMT

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 — Mon, 27 Jun 2011 07:45:45 GMT

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

kaushiknsolanki — Mon, 27 Jun 2011 08:10:29 GMT

Hi,

Make sure that you have proper Permission to modify and write the folder you mension in the store into.

Regards,

Kaushik Solanki

General Script Error while Storing QVD

Mon, 27 Jun 2011 09:56:54 GMT

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

kaushiknsolanki — Mon, 27 Jun 2011 10:16:36 GMT

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 — Mon, 27 Jun 2011 10:23:16 GMT

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 — Mon, 27 Jun 2011 10:27:46 GMT

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

General Script Error while Storing QVD

Mon, 27 Jun 2011 10:28:15 GMT

Then I'll have to check which fields are needed later in analysis. I'll drop remaining fields and check if I get any improvements.

Re: General Script Error while Storing QVD

Mon, 27 Jun 2011 10:35:15 GMT

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

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 10:54:40 GMT

Hi,

Apart from the SLEEP suggestion above, try with the following line just to check that the table has been created and loaded properly

drop table LEA_AGREEMENT_DTL_TEMP;

LET vRowsNo = NoOfRows('LEA_AGREEMENT_DTL'); // Stores the number of rows of the table
TRACE $(vRowsNo); // will show the value in the progress window or the debug window

Store LEA_AGREEMENT_DTL into D:\Qlik2Finance\Data\RawQVD\LEA_AGREEMENT_DTL.qvd;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: General Script Error while Storing QVD

Tue, 12 Jul 2011 00:37:55 GMT

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:

Re: General Script Error while Storing QVD

Fri, 15 Jul 2011 02:44:19 GMT

Adding sleep after store command is working..