4 Replies Latest reply: Dec 7, 2017 4:45 AM by Sarah Skelly RSS

    Script Execution Not Responding and then crashing.

    Sarah Skelly

      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
      (
      ooxml, embedded labels, table is [4]);

      LOAD [GGN O/S Loss Date], ResQL2, ResQL3, GOSMOV, REPDATE, ClaimRef, MRepDate, Team,
      [Claims Handler], year, month
      FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7. New Registered Claims.xlsx]
      (
      ooxml, embedded labels, table is [7]);

      LOAD ResQL2, REPDATE, ds_x1f, Team, [Claims Handler], GPDMOV, month
      FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12. Early Large Loss Payment.xlsx]
      (
      ooxml, embedded labels, table is [12]);

      LOAD ClaimRef, ResQL2, ResQL3, Team, [Claims Handler], nso, GINCMOV, [GGN O/S Loss Date], MTxDate,
      month
      FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16. Sections Closed in Month.xlsx]
      (
      ooxml, embedded labels, table is [16]);


      LOAD ds_x1f, ResQL2, Team, [Claims Handler], GINCMOV, nso, [GGN O/S Loss Date], month
      FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17. Closed Claims with Negative Incurred.xlsx]
      (
      ooxml, embedded labels, table is [17]);


      LOAD [Claim Master Ref], ResQL2, Team, [Claims Handler], month, GPDMOV, GOSMOV, GINCMOV, nso
      FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9. Large Claim Movements Greater Than 100K.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
      (
      ooxml, embedded labels, table is [4]);

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

      Drop table 4;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7. New Registered Claims.xlsx]
      (
      ooxml, embedded labels, table is [7]);

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

      Drop table 7;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7. New Registered Claims.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12. Early Large Loss Payment.xlsx]
      (
      ooxml, embedded labels, table is [12]);

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

      Drop table 7;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12. Early Large Loss Payment.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16. Sections Closed in Month.xlsx]
      (
      ooxml, embedded labels, table is [16]);

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

      Drop table 16;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16. Sections Closed in Month.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17. Closed Claims with Negative Incurred.xlsx]
      (
      ooxml, embedded labels, table is [17]);

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

      Drop table 17;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17. Closed Claims with Negative Incurred.xlsx]
      (
      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
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9. Large Claim Movements Greater Than 100K.xlsx]
      (
      ooxml, embedded labels, table is [9]);

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

      Drop table 9;

      LOAD * FROM
      [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9. Large Claim Movements Greater Than 100K.xlsx]
      (
      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

          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.
              Colin Albert

              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!

               

               

              debug LimitedLoad.jpg

              • Re: Script Execution Not Responding and then crashing.
                Sarah Skelly

                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7. New Registered Claims.xlsx]
                (
                ooxml, embedded labels, table is [7]);

                Concatenate
                LOAD ResQL2, REPDATE, ds_x1f, Team, [Claims Handler], GPDMOV, month
                FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12. Early Large Loss Payment.xlsx]
                (
                ooxml, embedded labels, table is [12]);

                Concatenate
                LOAD ClaimRef, ResQL2, ResQL3, Team, [Claims Handler], nso, GINCMOV, [GGN O/S Loss Date], MTxDate,
                month
                FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16. Sections Closed in Month.xlsx]
                (
                ooxml, embedded labels, table is [16]);

                Concatenate
                LOAD ds_x1f, ResQL2, Team, [Claims Handler], GINCMOV, nso, [GGN O/S Loss Date], month
                FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17. Closed Claims with Negative Incurred.xlsx]
                (
                ooxml, embedded labels, table is [17]);

                Concatenate
                LOAD [Claim Master Ref], ResQL2, Team, [Claims Handler], month, GPDMOV, GOSMOV, GINCMOV, nso
                FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9. Large Claim Movements Greater Than 100K.xlsx]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
                (
                ooxml, embedded labels, table is [4]);

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

                Drop table 4;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4.qvd]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7. New Registered Claims.xlsx]
                (
                ooxml, embedded labels, table is [7]);

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

                Drop table 7;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\7.qvd]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12. Early Large Loss Payment.xlsx]
                (
                ooxml, embedded labels, table is [12]);

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

                Drop table 12;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\12.qvd]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16. Sections Closed in Month.xlsx]
                (
                ooxml, embedded labels, table is [16]);

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

                Drop table 16;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\16.qvd]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17. Closed Claims with Negative Incurred.xlsx]
                (
                ooxml, embedded labels, table is [17]);

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

                Drop table 17;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\17.qvd]
                (
                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9. Large Claim Movements Greater Than 100K.xlsx]
                (
                ooxml, embedded labels, table is [9]);

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

                Drop table 9;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\9.qvd]
                (
                qvd);



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

                key1

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

              • Re: Script Execution Not Responding and then crashing.
                Gysbert Wassenaar

                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
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
                (
                ooxml, embedded labels, table is [4]);

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

                Drop table 4;

                LOAD * FROM
                [R:\Insight & Control\2017\Insight Work\Exception Reports\Current Summaries\4. Live Claims with Negative Incurred.xlsx]
                (
                ooxml, embedded labels, table is [4]
                );