Tutorial 1 | UK Schools Analysis.qvw

[#1]

Hi Everyone,

I want to start something different, something I don't think has been done on this forum before, but believe will add a lot of value. A little about my background, I work as a Commercial analyst and have little or no programming experience. I have spent an abundant amount of time on excel (like most) but a part from that, am still very new to the world of relationship databases and QV.

Someone once told me that the best way to learn is through trying to solve problems, and through the journey of completion, you learn about scripting, rules et cetera. This has helped so far, but for someone like me, I often doubt if I am doing things the right way and wondering whether I am learning anything new.

I find that once I get comfortable doing stuff a certain way, that it is hard to try new peices of code or build the report in a completely new way. I believe this to be true for a lot of people.

So enough waffling from me..............for now.... and to the point. This is an open discussion and every week I will uploading a new QV document (available from the downloads page) for us to discuss. Often there is code and ways of doing things that are new and unfamilar for me and a lot of people on this forum.

Through asking questions specific to a QV document and getting the knowledgeable community together, we get a holistic view on how this report was put together and why. Through this process, we learn and also critic what can be done better and so forth. I really hope you all buy into this, and if you think there is a better way I can go about this... please shout

So to start things off.

In the EDIT SCRIPT on the GSCEs page, there is a lot of commented out stuff which makes it a bit difficult to read, but from what I can see...not understand there is a lot of left joins and where not clauses. Exactly what is the person trying to achieve with what they have written. How can I make it easier for me to interpret and understand what is going on

[#2]

So I thought I would try my best to explain what I believe is being attempted in the GSCE tab. I will post snippets of the code in order and explain each. If I am off track, please advise and if you know of another method of achieving what the author of this document has done, that would be great too.

-----------------------------------------------------------------------------------------------------------------

TempSchools:

LOAD Column as School,

key

     FROM

     C:\Users\aby\Desktop\apps\Schools\GCSEs_1_8000.qvd

     (qvd)

          Where not(match(Column,

               '% time lost through absence:','% with special needs:','Admissions policy:','Age range:','Average point score:',

               'County:','Gender:','Institution in A-level tables:','Number of pupils:','Post code:',

               'Pupils eligible to sit exams:','School type:','Status:','Town:','Value-added score:'))

AND Column<>'';

                                             Left Join

LOAD DataValue as [% time lost through absence],

key

     FROM

     C:\Users\aby\Desktop\apps\Schools\GCSEs_1_8000.qvd

     (qvd)

          Where not(match(Column,

               '% with special needs:','Admissions policy:','Age range:','Average point score:','County:',

               'Gender:','Institution in A-level tables:','Number of pupils:','Post code:',

               'Pupils eligible to sit exams:','School type:','Status:','Town:','Value-added score:'))

AND Column<>'';

                                             Left Join

LOAD DataValue as [Age Range],

key

     FROM

     C:\Users\aby\Desktop\apps\Schools\GCSEs_1_8000.qvd

     (qvd)

          Where not(match(Column,

               '% time lost through absence:','% with special needs:','Admissions policy:',,'Average point score:',

               'County:','Gender:','Institution in A-level tables:','Number of pupils:','Post code:',

               'Pupils eligible to sit exams:','School type:','Status:','Town:','Value-added score:'))

AND Column<>'';

-----------------------------------------------------------------------------------------------------------------

Thoughout the GSCE tab, the author of the document has used LEFT JOINS and is creating a TEMP TABLE called TEMPSCHOOLS. He is loading the specific columns from a QVD called GSCEs_1_8000.qvd.

LEFT JOIN matches everything in column 1 with what it can find in column 2. If it cant match anything in column 2 it still will load column 1 but will have a NULL value in colum 2.

He then specifies a WHERE NOT (MATCH). What he is trying to do is exclude from column the following  fields that he does not want to appear in his temp table as well as a AND Column<>' ', which is saying where the column is not blank in otherwords.

What I don't understand is that he loads a few DATAVALUE colums with a new alias but they are from the same document. Is this not the same field just with a new name. PLEASE CAN SOMEONE HELP EXPLAIN THAT. Later this afternoon I will come back and past the last section where he loads his final table TEMPFINALDATA

Cheerio for now,

Byron

[#3]

Good morning all,

The final script in the GSCE tab is the TEMPFINALDATA.

----------------------------------------------------------------------------------------------

TempFinalData:

Load *,

'GCSE-'&key as SchoolKey,

'GCSE' as Level,

'G-'&key as finalkey

Resident TempSchools

Where [% time lost through absence]<>School

AND [Age Range]<>School

AND Gender<>School

AND [No Of Pupils]<=9999

AND [School Type]<>School

AND [Status]<>School;

;

Drop Table TempSchools;

----------------------------------------------------------------------------------------------

The user loads all fields from the temp tables that were joined as well as 3 addtional fields, schoolkey, level and finalkey. He loads these by concatenating text strings as well as certain fields.

Instead of using FROM he uses RESIDENT as he is calling the information from this table as it is a temp table. Once again there is a where clause and eventually he drops the TEMPSCHOOL table and is now left with his final table

TEMPFINALDATA. I will be moving onto the ALevel tab next. On another note, next week, I will be uploading a new document for discussion. This weekend I will complete the UK SCHOOLS ANALYSIS

[#4]

The A LEVEL tab script is pretty much identical to that of GSCE. The only difference, the actual data being imported into the temp table. With that we move onto the SCORES tab

The user is creating a Temp table called TempGSCEScore. The first part of the script is a CROSS TABLE

-----------------------------------------------------------------------------------------------

TempGCSEScore:

CrossTable(Year, Score, 2)

LOAD SchoolKey,

[Track record:] as ScoreType,

[2006],

[2007],

[2008],

[2009]

FROM

C:\Users\aby\Desktop\apps\Schools\GCSEScores.qvd

(qvd);

-----------------------------------------------------------------------------------------------

I have never used CROSS TABLES before, and it was a very interesting and useful finding out what they do. Pretty much most EXCEL spreedsheets resemble a cross table data. The QV tutorial that you got from downloading QV explains Cross tables very nicely.

OK, so the 3 items in the brackets (Year, Score, 2) is saying that YEAR is the header (the item name for columns), SCORE is the data field that would be in the same column under YEAR. 2 is the number of qualifer fields preceding the load and to be converted to generic form. I would strongly recommend reading the tutorial for this section as the captions and explaination is really simple to understand. One very important use for cross table for users is that we are now able to show a new variety of list boxes and display information in a different and more simplified way

[#5]

If anyone knows of another or better way to complete any of the above actions please let us know

[#6]

---------------------------------------------------------------------------------------------------------

Scores:

Load 1 as Dummy,

SchoolKey,

Year,

if(Score='NA',0,Score) as Score,

ScoreType

Resident TempGCSEScore;

Drop Table TempGCSEScore;

Drop Field Dummy;

TempALEVELSCORES:

CrossTable(Year, Score, 2)

LOAD key,

[Track record:] as ScoreType,

[2006],

[2007],

[2008],

[2009]

FROM

C:\Users\aby\Desktop\apps\Schools\ALEVELScores.qvd

(qvd);

CONCATENATE (Scores)

//data:

Load 1 as Dummy,

'AL-'&key as SchoolKey,

Year,

if(Score='NA',0,Score) as Score,

ScoreType

Resident TempALEVELSCORES;

//

//Drop Field Dummy;

drop table TempALEVELSCORES;

----------------------------------------------------------------------------------------------------------

It seems as the user wanted to create a table from the cross table he created and wanted to specify exactly what he wanted to see. The only thing I can that is different is that he cleaned up the score column with an if statement. The reason for the dummy field is beyond me If someone could assist with that, it would be greatly appreciated.

Concatenate joins two tables and even if they may not necessarily have the same fields, a forced concatenation can be done by using the prefix concatenate.

I will be trying to replicate what he has done with this script using some fictitious data on excel and upload the qvd with the excel data table

[#7]

Ok I've Tried to use the same script above with my own data and I am getting an error with the last piece. I am posting this on a new thread, hoping that someone can resolve this quite quickly. I've attached the 2 documents i used

[pat_agen · Accepted solution]

hi,

this is in reply to your last post.

To debug what is happening use the command exit script. Placed correctly it will let you see in stages just waht qlikview is doing with your data and gives the answers to some of your questions from above.

Your first load creates a table called GSCE with 4 fields: SchoolKey, ScoreType, Yearn, Scores.

the second step is just going to change the data slightly for the field Scores with this line

If(Scores='NA',0,Scores) as Scores,

This table which the script writer wants to call Scores ahs exactly the same structure as GSCEs.

as qlikview works with one table at a time and expects/demands to find unqiueness of field names to all intents and purposes the tables Scores and GSCE are the same object. To get round this the fiedl Dummy is thrown in. thsi forces the creation of the table Scores.

We then drop the field Dummy and the table GSCE.

Step 3 you relaod data from xl but into exactly the same format as the previos table "Scores". Qlikview will not create a new table called A-level for the reasons stated above.

Step4: you now try and adda the contents of the table "A-level" to Scores and qlikview tells you that te table cannot be found.

So:

lessons to be learnt: think of one table at a time, use od Dummy to force creation of new table. Break up your script to debug, use the control T button to see the data strcuture you are building; use the debugger mode.

workaround. leave the field Dummy in place and just drop it at the end.

[#9]

Thanks Pat, that was really useful and insightful. I understand now why my table wasnt loading correctly and after I made some changes based on your comments, everything worked perfectly.

Thanks for taking the time to add valuable content to this thread. Much appreciated man

[#10]

I did go through the rest of the script this weekend and there is nothing new that has been in each tab. Eventually all temp tables are dropped and a final table kept.

In my opinion, and no disrepect to the author of the document, I did not like the way the tables were put together graphically using the tools available to them with QV (referring to the actual report now). The information that is important does not really stand out, and I just find it a bit simple. The map option may appear cool, but adds no real value as there are more then enough list boxes to identify which school you looking for.

I will be starting a new thread today. Tutorial 2: TV Finder. Graphically, I love what was done with this report, its simple and to the point.