Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
BI Consultant
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.
BI Consultant
Hi,
Make sure that you have proper Permission to modify and write the folder you mension in the store into.
Regards,
Kaushik Solanki
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.
Hi,
I feel the no of fields is a problem.
Try storing less no of fields.
Regards,
Kaushik Solanki
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.
BI Consultant
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.
BI Consultant
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.
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;
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.
BI Consultant