topic Re: Load script freezing around if statement in QlikView

Load script freezing around if statement

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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]

;

Re: Load script freezing around if statement

Anil_Babu_Samineni — Thu, 07 Dec 2017 12:53:53 GMT

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

sunny_talwar — Thu, 07 Dec 2017 12:58:36 GMT

Are you potentially doing a many to many join?

Re: Load script freezing around if statement

sunny_talwar — Thu, 07 Dec 2017 13:00:21 GMT

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

Re: Load script freezing around if statement

jessica_webb — Thu, 07 Dec 2017 13:00:45 GMT

Can you explain further please, Sunny?

Re: Load script freezing around if statement

sunny_talwar — Thu, 07 Dec 2017 13:01:49 GMT

I just did... read above

Re: Load script freezing around if statement

jessica_webb — Thu, 07 Dec 2017 13:07:44 GMT

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

sunny_talwar — Thu, 07 Dec 2017 13:13:35 GMT

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.

Re: Load script freezing around if statement

jessica_webb — Thu, 07 Dec 2017 13:15:53 GMT

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

Re: Load script freezing around if statement

sunny_talwar — Thu, 07 Dec 2017 13:21:42 GMT

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;

Re: Load script freezing around if statement

sunny_talwar — Thu, 07 Dec 2017 13:22:07 GMT

Didn't go about writing the whole thing, but you get the idea

Re: Load script freezing around if statement

jessica_webb — Thu, 07 Dec 2017 13:26:09 GMT

Once again Sunny, you've saved my application

Thank you so much!

Although couldn't get the 'Rename' function to work - but that's they least of my worries

Thanks again,

Jess

Re: Load script freezing around if statement

sunny_talwar — Thu, 07 Dec 2017 13:31:32 GMT

My bad, I missed a key work in it... try this

RENAME Table NEWDATA to DATA;