Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jessica_webb
Contributor II

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]

;

Tags (1)
1 Solution

Accepted Solutions

Re: Load script freezing around if statement

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;

12 Replies

Re: Load script freezing around if statement

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

Re: Load script freezing around if statement

Are you potentially doing a many to many join?

Re: Load script freezing around if statement

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
Contributor II

Re: Load script freezing around if statement

Can you explain further please, Sunny?

Re: Load script freezing around if statement

I just did... read above

jessica_webb
Contributor II

Re: Load script freezing around if statement

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?

Re: Load script freezing around if statement

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
Contributor II

Re: Load script freezing around if statement

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

Re: Load script freezing around if statement

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;

Community Browser