Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

Load script freezing around if statement

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]

;

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

12 Replies
Anil_Babu_Samineni

This one is okay to proceed as i believe.. Not sure how to over come using If..else instead ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Are you potentially doing a many to many join?

sunny_talwar

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

jessica_webb
Creator III
Creator III
Author

Can you explain further please, Sunny?

sunny_talwar

I just did... read above

jessica_webb
Creator III
Creator III
Author

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?

sunny_talwar

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.

jessica_webb
Creator III
Creator III
Author

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:

      

RefLAESTABNameLocationSTUDY LAHOME LAStatus
ABC123CroydonBarking and DagenhamBarking and DagenhamBarking and Dagenham-
ABC345CroydonBarking and DagenhamBarking and DagenhamBarking and Dagenham-
CDE345CroydonCroydonBarking and DagenhamCroydonRes
EFG567CroydonCroydonBarnetCroydonRes
GHI789Croydon-BedfordCroydonRes
IJK910Croydon-BirminghamCroydonRes
EFG12CroydonCroydonCroydonCroydonRes/Stu
GHI234Croydon-OxfordshireCroydonRes
GHI456Croydon-SurreyCroydonRes

I've completed 'HOME LA' and 'Status' with what I would want to see...

sunny_talwar

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;