12 Replies Latest reply: Dec 7, 2017 8:31 AM by Sunny Talwar RSS

    Load script freezing around if statement

    Jessica Webb

      Desperate need of help here!!

       

      My load script keeps freezing around the two coloured sections below. If I comment out these sections, it loads ok. But as soon as they're back in, it freezes!!

       

      Any ideas why this would be? Or any alternative ways of achieving the same thing?

       

      Thanks,
      Jess

       

       

       

      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 MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

      SET FirstWeekDay=0;

      SET BrokenWeeks=1;

      SET ReferenceDay=0;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-GB';

       

       

      DATA:

      LOAD Ref,

           Trigger_New,

           LAESTAB,

           LEFT(LAESTAB,3) as [LA (code)],

           Gender,

            IF(EthnicGroupMajor='NULL','Unknown',EthnicGroupMajor) as Ethnicity,

           IF(FSMEligible='NULL','Unknown',FSMEligible) as FSM,

           GCSEPts_New,

           AvgKS4_New,

           ALEV,

           ACAD,

           APPGEN,

           TECH,

           TotPtse,

           TotPtse_ALEV,

           TotPtse_ACAD,

           TotPtse_APPGEN,

           TotPtse_TECH,

           TotEnts,

           TotEnts_ALEV,

           TotEnts_ACAD,

           TotEnts_APPGEN,

           TotEnts_TECH,

           HOME_LA,

           SOACode,

           KS5_GNUMBER as Code,

           KS5_MAPPING,

           ExamSeason

      FROM

      [T:\Qlikview Source Data\2017-18\Croydon\Croydon - ILR + NPD 2.xlsx]

      (ooxml, embedded labels, table is [Croydon - ILR + NPD Results])

      WHERE Trigger_New=1;

       

       

      //LA of school they attend

      LEFT JOIN (DATA)

           LOAD 

           [LA (code)],

           [LA (name)] as [STUDY LA]

      FROM

      [T:\Dougal\Data\Edubase Extract\extract - June 2017.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

       

       

       

      //LA of where they live    

      LEFT JOIN (DATA)

      LOAD LSOA11CD  as SOACode,

      LAD15CD,

           LAD15NM as Location

      FROM

      [T:\Dougal\Data\SOA code etc\LLSOA to LEP.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

       

       

       

      //Alternative LA of where they live

      LEFT JOIN (DATA)

      LOAD

           DfENumber as HOME_LA,

           Name

      FROM

      [T:\Dougal\Data\Edubase Extract\LA Codes.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      //Final LA of where they live

      LEFT JOIN (DATA)

      LOAD

      Ref,

      LAESTAB,

      IF(SOACode<>'NULL',Location,IF(HOME_LA<>'NULL', Name,'Unknown')) as [HOME LA]

      Resident DATA;

       

       

      Drop Fields Location, Name;

       

       

      //Status

      LEFT JOIN (DATA)

      LOAD

      Ref,

      LAESTAB,

      IF([STUDY LA]='Croydon' and [HOME LA]='Croydon','Res/Stu',IF([STUDY LA]='Croydon' and [HOME LA]<>'Croydon','Stu',IF([STUDY LA]<>'Croydon' and [HOME LA]='Croydon','Res'))) as Status

      Resident DATA;

       

       

       

       

      OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CurriculumMap_NEW;Data Source=LP-PC-07\LPUKDB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LP-PC-08;Use Encryption for Data=False;Tag with column collation when possible=False];

      LEFT JOIN (DATA)

      LOAD ID,

          Code,

          Name as Qual,

          MapsTo as SubjectID,

          Provider,

          ProgType;

      SQL SELECT *

      FROM "CurriculumMap_NEW".dbo.QAN;

       

       

      LEFT JOIN (DATA)

      LOAD SubjectID,

          SubjectName,

          SubjectType,

          SSATopLevel,

          Facilitating,

          IsTechLevel,

          IsAppliedGeneral,

          Size,

          QualProvider;

      SQL SELECT *

      FROM "CurriculumMap_NEW".dbo.Subjects;

       

       

       

       

      LEFT JOIN (DATA)

      LOAD

           [SSA Code] as SSATopLevel,

           [SSA Lower Level]

      FROM

      [T:\Dougal\Data\New accountability measures + A Level reform\Subjects and Qualifications Info\SSA Codes.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

       

       

      DROP FIELD

      [ACAD],

      [ALEV],

      [APPGEN],

      [Code],

      [ExamSeason],

      [Facilitating],

      [ID],

      [IsAppliedGeneral],

      [IsTechLevel],

      [KS5_MAPPING],

      [LA (code)],

      [LAD15CD],

      [Provider],

      [SSATopLevel],

      [Size],

      [SubjectID],

      [TECH],

      [TotEnts],

      [TotPtse],

      [Trigger_New]

      ;