General Script Error

[#1]

Hi, I am running a script via QlikView 8.5 Developer Application to pull in all transactional data for one quarter and the script fails every time in the middle. It doesnt complete and logs do not have any error messages except for a generic statement- "General Script error. Execution Failed". Nothing on the Windows Event Viewer logs either.

The process does not page and we have 512GB of memory with 48-cores box. Can you please share your thoughts as to why this could happen?

Below is the script for your reference - thank you for your time!!

TempTrans:
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0701.qvd (qvd)
;

TempTrans2:
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0702.qvd (qvd)
;

TempTrans3:
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0703.qvd (qvd)
;


StoreVisit:
LOAD DISTINCT
tranid,
cardnum,
tranid & '-' & cardnum as VisitKey,
1 as StoreVisit
RESIDENT TempTrans;

// Completes up until here and fails before executing the below script

LEFT JOIN (TempTrans)
LOAD
tranid,
cardnum,
VisitKey
RESIDENT StoreVisit;

DROP FIELD tranid, cardnum from StoreVisit;



Trans:
LOAD
disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
if(cardnum & '-' & tranid <> previous(cardnum & '-' & tranid),1) as StoreVisit,
tran_dt,
cardnum,
1 as TransCnt
RESIDENT TempTrans
ORDER BY cardnum, tranid;

DROP TABLE TempTrans;

[disqr_rm]

The problem is with your "ORDER BY" statement.

Try something like this:

TempTrans:
LOAD *, tranid & '/' & cardnum as VisitKey;
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0701.qvd (qvd)
;

CONCATENATE(TempTrans)
LOAD *, tranid & '/' & cardnum as VisitKey;
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0702.qvd (qvd)
;

CONCATENATE(TempTrans)
LOAD *, tranid & '/' & cardnum as VisitKey;
LOAD disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
tran_dt,
cardnum
FROM ..\..\..\datasource\Transactions\Trans0703.qvd (qvd)
;

Trans:
LOAD
VisitKey.
disc_code,
disc_type,
r_weight,
[store],
terminal,
upc,
ext_price,
quantity,
disc_qty,
disc_amt,
tranid,
if(VisitKey <> previous(VisitKey),1) as StoreVisit,
tran_dt,
cardnum,
1 as TransCnt
RESIDENT TempTrans;

DROP TABLE TempTrans;

I could not verify the answer as have no date. If this doesn't work for you, please provide some sample data.

Thanks
Rakesh

[#3]

Thanks for your reply Rakesh.

If I use dual-load statement, sorry I don't know what its called pardon my terminology, like LOAD *; LOAD ... RESIDENT - your data load is not optimized so run-time is longer. One of QT consultant recommended to use RESIDENT LOAD and once in memory perform transformations etc so the initial load time is much faster.

Also, an ORDER BY is needed because before comparing VisitKey column values I need it to be sorted on transaction to avoid data issues - but that was a good suggestion I'll remember to check the order by statement in future.

The issue was my LEFT JOIN, when I changed it to a normal LOAD it ran much faster with no failure error messages.

So, instead of

StoreVisit:
LOAD DISTINCT
tranid,
cardnum,
tranid & '-' & cardnum as VisitKey,
1 as StoreVisit
RESIDENT TempTrans;

LEFT JOIN (TempTrans)
LOAD
tranid,
cardnum,
VisitKey
RESIDENT StoreVisit;

I modified it to

TempTrans1:
LOAD
*,
tranid & '-' & cardnunm as VisitKey
RESIDENT TempTrans;
DROP TABLE TempTrans;

Thank you again for your help.