topic Re: Script Execution Not Responding and then crashing. in QlikView

Script Execution Not Responding and then crashing.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am loading 17 excel files into the script, each have variable names overlapping but I want to keep the data separate so I am renaming the variables.

I have loaded 6 in and had no problems, however I cannot load anymore in without the script not responding and QV crashing.

Here is my script so far that is working fine.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='€#,##0.00;-€#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-IE';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Set RSAPurple= RGB(90,45,127);
Set RSAPurple1= RGB(123,87,153);
Set RSAPurple2= RGB(156,129,178);
Set RSAPurple3= RGB(189,171,204);
Set RSAPurple4= RGB(222,213,229);

SET RSACream= RGB(232,230,220);
SET RSACream1= RGB(237,235,227);
SET RSACream2= RGB(241,240,234);
SET RSACream3= RGB(246,245,241);
SET RSACream4= RGB(250,250,248);

SET RSAAqua= RGB(108,177,182);
SET RSADarkAqua= RGB(86,144,153);
SET RSAAqua1= RGB(137,193,197);
SET RSAAqua2= RGB(167,208,211);
SET RSAAqua3= RGB(196,224,226);
SET RSAAqua4= RGB(226,239,240);

SET RSAGreen= RGB(173,187,160);
SET RSAGreen1= RGB(189,201,179);
SET RSAGreen2= RGB(206,214,198);
SET RSAGreen3= RGB(222,289,217);
SET RSAGreen4= RGB(239,241,236);

SET RSAMagenta= RGB(205,0,132);
SET RSAMagenta1= RGB(223,93,177);
SET RSAMagenta2= RGB(255,102,181);
SET RSAMagenta3= RGB(235,153,206);
SET RSAMagenta4= RGB(245,204,230);

LOAD ds_x1f, ResQL2, Team, [Claims Handler], month, GINCMOV
FROM

(ooxml, embedded labels, table is [4]);

LOAD [GGN O/S Loss Date], ResQL2, ResQL3, GOSMOV, REPDATE, ClaimRef, MRepDate, Team,
[Claims Handler], year, month
FROM

(ooxml, embedded labels, table is [7]);

LOAD ResQL2, REPDATE, ds_x1f, Team, [Claims Handler], GPDMOV, month
FROM

(ooxml, embedded labels, table is [12]);

LOAD ClaimRef, ResQL2, ResQL3, Team, [Claims Handler], nso, GINCMOV, [GGN O/S Loss Date], MTxDate,
month
FROM

(ooxml, embedded labels, table is [16]);

LOAD ds_x1f, ResQL2, Team, [Claims Handler], GINCMOV, nso, [GGN O/S Loss Date], month
FROM

(ooxml, embedded labels, table is [17]);

LOAD [Claim Master Ref], ResQL2, Team, [Claims Handler], month, GPDMOV, GOSMOV, GINCMOV, nso
FROM

(ooxml, embedded labels, table is [9]);

4:

LOAD
ds_x1f as ds_x1f.4,
ResQL2 as ResQL2.4,
Team as Team.4,
[Claims Handler] as [Claims Handler.4],
month as month.4,
GINCMOV as GINCMOV.4

FROM

(ooxml, embedded labels, table is [4]);

store 4 into $(QVDPath)4.qvd (qvd);

Drop table 4;

LOAD * FROM

(ooxml, embedded labels, table is [4]);

7:

LOAD
[GGN O/S Loss Date] as [GGN O/S Loss Date.7],
ResQL2 as ResQL2.7,
ResQL3 as ResQL3.7,
GOSMOV as GOSMOV.7,
REPDATE as REPDATE.7,
ClaimRef as ClaimRef.7,
MRepDate as MRepDate.7,
Team as Team.7,
[Claims Handler] as [Claims Handler.7],
year as year.7,
month as month.7

FROM

(ooxml, embedded labels, table is [7]);

store 7 into $(QVDPath)7.qvd (qvd);

Drop table 7;

LOAD * FROM

(ooxml, embedded labels, table is [7]);

12:

LOAD
ResQL2 as ResQL2.12,
REPDATE as REPDATE.12,
ds_x1f as ds_x1f.12,
Team as Team.12,
[Claims Handler] as [Claims Handler.12],
GPDMOV as GPDMOV.12,
month as month.12

FROM

(ooxml, embedded labels, table is [12]);

store 7 into $(QVDPath)7.qvd (qvd);

Drop table 7;

LOAD * FROM

(ooxml, embedded labels, table is [12]);

16:

LOAD
ClaimRef as ClaimRef.16,
ResQL2 as ResQL2.16,
ResQL3 as ResQL3.16,
Team as Team.16,
[Claims Handler] as [Claims Handler.16],
nso as nso.16,
GINCMOV as GINCMOV.16,
[GGN O/S Loss Date] as [GGN O/S Loss Date.16],
MTxDate as MTxDate.16,
month as month.16

FROM

(ooxml, embedded labels, table is [16]);

store 16 into $(QVDPath)16.qvd (qvd);

Drop table 16;

LOAD * FROM

(ooxml, embedded labels, table is [16]);

17:

LOAD
ds_x1f as ds_x1f.17,
ResQL2 as ResQL2.17,
Team as Team.17,
[Claims Handler] as [Claims Handler.17],
GINCMOV as GINCMOV.17,
nso as nso.17,
[GGN O/S Loss Date] as [GGN O/S Loss Date.17],
month as month.17

FROM

(ooxml, embedded labels, table is [17]);

store 17 into $(QVDPath)17.qvd (qvd);

Drop table 17;

LOAD * FROM

(ooxml, embedded labels, table is [17]);

9:

LOAD
[Claim Master Ref] as [Claim Master Ref.9],
ResQL2 as ResQL2.9,
Team as Team.9,
[Claims Handler] as [Claims Handler.9],
month as month.9,
GPDMOV as GPDMOV.9,
GOSMOV as GOSMOV.9,
GINCMOV as GINCMOV.9,
nso as nso.9

FROM

(ooxml, embedded labels, table is [9]);

store 9 into $(QVDPath)9.qvd (qvd);

Drop table 9;

LOAD * FROM

(ooxml, embedded labels, table is [9]);

I am new to QV and would appreciate anybody's help?

Thanks

Re: Script Execution Not Responding and then crashing.

marcus_sommer — Wed, 06 Dec 2017 16:47:59 GMT

If you used the debugger with a reduced number of records - maybe 5 - you will see that you get multiple synthetic keys and maybe even some circular loops between your tables. Synthetic keys do work quite often but could need a lot of resources and may lead to a crash. Circular loops will always lead to wrong results.

This meant you will need to identify keys between your tables and rename all fields appropriate and/or merging some of your tables. Here you will find a good starting point for Get started with developing qlik datamodels.

- Marcus

Re: Script Execution Not Responding and then crashing.

Gysbert_Wassenaar — Wed, 06 Dec 2017 16:58:46 GMT

You rename fields. Then store and drop the table and then you load the orginal again without renaming the fields.

4:
LOAD
ds_x1f as ds_x1f.4,
ResQL2 as ResQL2.4,
Team as Team.4,
[Claims Handler] as [Claims Handler.4],
month as month.4,
GINCMOV as GINCMOV.4

FROM

(ooxml, embedded labels, table is [4]);

store 4 into $(QVDPath)4.qvd (qvd);

Drop table 4;

LOAD * FROM

(ooxml, embedded labels, table is [4]);

Re: Script Execution Not Responding and then crashing.

Colin-Albert — Wed, 06 Dec 2017 17:00:29 GMT

If you select the Debug option from the script editor, you can use Limited Load to just load a few rows from each table to identify the problem.

I would also suggest using an "EXIT SCRIPT ;" command half way through your script, so you just load a couple of tables, fix any issues, then move the "exit script" down and test the next table again with a limited load.

Finally don't forget to remove or comment out the exit script line!

This will be much easier than trying to untangle any synthetic keys and loops created by loading all tables.

Fixing the issues as you go is much easier!

Re: Script Execution Not Responding and then crashing.

Anonymous — Thu, 07 Dec 2017 09:45:14 GMT

Thank you for your replay, it was a great help.

This is what I have done so far, is there a better/more efficient way?

Thanks

allreports:

LOAD ds_x1f, ResQL2, Team, [Claims Handler], month, GINCMOV
FROM

(ooxml, embedded labels, table is [4]);

Concatenate
LOAD [GGN O/S Loss Date], ResQL2, ResQL3, GOSMOV, REPDATE, ClaimRef, MRepDate, Team,
[Claims Handler], year, month
FROM

(ooxml, embedded labels, table is [7]);

Concatenate
LOAD ResQL2, REPDATE, ds_x1f, Team, [Claims Handler], GPDMOV, month
FROM

(ooxml, embedded labels, table is [12]);

Concatenate
LOAD ClaimRef, ResQL2, ResQL3, Team, [Claims Handler], nso, GINCMOV, [GGN O/S Loss Date], MTxDate,
month
FROM

(ooxml, embedded labels, table is [16]);

Concatenate
LOAD ds_x1f, ResQL2, Team, [Claims Handler], GINCMOV, nso, [GGN O/S Loss Date], month
FROM

(ooxml, embedded labels, table is [17]);

Concatenate
LOAD [Claim Master Ref], ResQL2, Team, [Claims Handler], month, GPDMOV, GOSMOV, GINCMOV, nso
FROM

(ooxml, embedded labels, table is [9]);

handlerinfo:
NoConcatenate
LOAD Distinct
Team, [Claims Handler], month,
AutoNumber (Team & [Claims Handler] & month) as key1

Resident
allreports;

DROP Table allreports;

STORE handlerinfo into $(QVDPath)handlerinfo.qvd (qvd);

drop Table handlerinfo;

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

UNQUALIFY key1;

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

4:

LOAD
ds_x1f as ds_x1f.4,
ResQL2 as ResQL2.4,
Team as Team.4,
[Claims Handler] as [Claims Handler.4],
AutoNumber (Team & [Claims Handler] & month) as key1,
month as month.4,
GINCMOV as GINCMOV.4

FROM

(ooxml, embedded labels, table is [4]);

store 4 into $(QVDPath)4.qvd (qvd);

Drop table 4;

LOAD * FROM

(qvd);

7:

LOAD
[GGN O/S Loss Date] as [GGN O/S Loss Date.7],
ResQL2 as ResQL2.7,
ResQL3 as ResQL3.7,
GOSMOV as GOSMOV.7,
REPDATE as REPDATE.7,
AutoNumber (Team & [Claims Handler] & month) as key1,
ClaimRef as ClaimRef.7,
MRepDate as MRepDate.7,
Team as Team.7,
[Claims Handler] as [Claims Handler.7],
year as year.7,
month as month.7

FROM

(ooxml, embedded labels, table is [7]);

store 7 into $(QVDPath)7.qvd (qvd);

Drop table 7;

LOAD * FROM

(qvd);

12:

LOAD
ResQL2 as ResQL2.12,
REPDATE as REPDATE.12,
ds_x1f as ds_x1f.12,
AutoNumber (Team & [Claims Handler] & month) as key1,
Team as Team.12,
[Claims Handler] as [Claims Handler.12],
GPDMOV as GPDMOV.12,
month as month.12

FROM

(ooxml, embedded labels, table is [12]);

store 12 into $(QVDPath)12.qvd (qvd);

Drop table 12;

LOAD * FROM

(qvd);

16:

LOAD
ClaimRef as ClaimRef.16,
ResQL2 as ResQL2.16,
ResQL3 as ResQL3.16,
Team as Team.16,
AutoNumber (Team & [Claims Handler] & month) as key1,
[Claims Handler] as [Claims Handler.16],
nso as nso.16,
GINCMOV as GINCMOV.16,
[GGN O/S Loss Date] as [GGN O/S Loss Date.16],
MTxDate as MTxDate.16,
month as month.16

FROM

(ooxml, embedded labels, table is [16]);

store 16 into $(QVDPath)16.qvd (qvd);

Drop table 16;

LOAD * FROM

(qvd);

17:

LOAD
ds_x1f as ds_x1f.17,
ResQL2 as ResQL2.17,
Team as Team.17,
[Claims Handler] as [Claims Handler.17],
GINCMOV as GINCMOV.17,
AutoNumber (Team & [Claims Handler] & month) as key1,
nso as nso.17,
[GGN O/S Loss Date] as [GGN O/S Loss Date.17],
month as month.17

FROM

(ooxml, embedded labels, table is [17]);

store 17 into $(QVDPath)17.qvd (qvd);

Drop table 17;

LOAD * FROM

(qvd);

9:

LOAD
[Claim Master Ref] as [Claim Master Ref.9],
ResQL2 as ResQL2.9,
Team as Team.9,
[Claims Handler] as [Claims Handler.9],
month as month.9,
AutoNumber (Team & [Claims Handler] & month) as key1,
GPDMOV as GPDMOV.9,
GOSMOV as GOSMOV.9,
GINCMOV as GINCMOV.9,
nso as nso.9

FROM

(ooxml, embedded labels, table is [9]);

store 9 into $(QVDPath)9.qvd (qvd);

Drop table 9;

LOAD * FROM

(qvd);

handlerinfoqvd:
LOAD Team, [Claims Handler], month,

key1

from $(QVDPath)handlerinfo.qvd (qvd);