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
(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
(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
(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
(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
(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]
;
