Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
;
Something like this
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
NEWDATA:
LOAD *,
IF(SOACode<>'NULL',Location,IF(HOME_LA<>'NULL', Name,'Unknown')) as [HOME LA]
Resident DATA;
DROP Table DATA;
Drop Fields Location, Name;
RENAME NEWDATA to DATA;
This one is okay to proceed as i believe.. Not sure how to over come using If..else instead ??
Are you potentially doing a many to many join?
Check if Ref and LAESTAB together forms the unique key for your DATA Table or not... if they don't then you might be doing a cartesian join... which depending on how bad it is, may never end
Can you explain further please, Sunny?
I just did... read above
It seems possible that that's what's happening... it managed to load once and took the file from 12,000KB to 20,000KB...
Do I need to add another field to my resident load to ensure the if statement is only applying to unique rows?
There is few things you can do....
1) Use Unique Identifier field from DATA field to do a join
2) Use all fields to do a join (hoping that all fields together will form a unique indentifier). Won't recommend this
3) Use a resident load to calculate IF(SOACode<>'NULL',Location,IF(HOME_LA<>'NULL', Name,'Unknown')) as [HOME LA]
4) Use Mapping Load if you truly think that Ref and LAESTAB determine the correct value of HOME LA.
I thought I already was doing number 3...
I am confident that Ref and LAESTAB are unique together, but I need to try something else as it's not working for me and I've got to get this finished!!
Would you be able to explain what I need to do differently for number 3?
In case it helps, this is what the simplified data table would look like:
Ref | LAESTAB | Name | Location | STUDY LA | HOME LA | Status |
ABC | 123 | Croydon | Barking and Dagenham | Barking and Dagenham | Barking and Dagenham | - |
ABC | 345 | Croydon | Barking and Dagenham | Barking and Dagenham | Barking and Dagenham | - |
CDE | 345 | Croydon | Croydon | Barking and Dagenham | Croydon | Res |
EFG | 567 | Croydon | Croydon | Barnet | Croydon | Res |
GHI | 789 | Croydon | - | Bedford | Croydon | Res |
IJK | 910 | Croydon | - | Birmingham | Croydon | Res |
EFG | 12 | Croydon | Croydon | Croydon | Croydon | Res/Stu |
GHI | 234 | Croydon | - | Oxfordshire | Croydon | Res |
GHI | 456 | Croydon | - | Surrey | Croydon | Res |
I've completed 'HOME LA' and 'Status' with what I would want to see...
Something like this
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
NEWDATA:
LOAD *,
IF(SOACode<>'NULL',Location,IF(HOME_LA<>'NULL', Name,'Unknown')) as [HOME LA]
Resident DATA;
DROP Table DATA;
Drop Fields Location, Name;
RENAME NEWDATA to DATA;