Now, reopen Executive Dashboard.qvw in the examples. Choose File | Edit Script.
Although there is not much left to learn from the actual scripts in the CFO executive dashboard, we can see from the very first statement in the script that we are loading from another QlikView document with the same name em dash except with lowercase letters. The following is the first statement in the script:
Binary [executive dashboard.qvw];
The binary statement is used for loading the data from another QlikView document. It does not load the layout information or variables. Only one binary statement is allowed in the script, and it can only be put as the first statement of a script. You can use relative locations, and there is a checkbox to get QlikView to generate a relative location statement. This allows you to move your QlikView operations to folders on another computer or another server without extensive editing of script load paths.
At the bottom of the script interface, we find the area where we can load more data of different types. Using the wizard for all the different Table files at the bottom of the script allows QlikView to build the script for you. You will not have to edit the script unless you want to rename the fields. But learning what a script for a particular type of file looks like can be helpful in analyzing what someone else has done. It can even assist you in adjusting your data loads to meet your needs.
Figure 4-3: File types for loading
Here, we see that we can load other QlikView files by saving them as QlikView Data Files and QlikView Exchange Files. So we are not truly limited to one QlikView file, that is, one of the document (.qvw) type. A QlikView Data (QVD) file is a file containing a table of data exported from QlikView. QVD is a native QlikView format and can only be written to and read by QlikView. QVX, the exchange file format, is a stream or text file for high-performance input and output from QlikView.
The following are three examples of how the load script section for loading from a QVD file might appear. Notice that in the third load example, we will be renaming our dimension fields a, b, and c:
load * from myAssets.qvd (qvd); load AssetNo, AssetDesc, AssetValue from myAssets.qvd (qvd); load AssetNo as a, AssetDesc as b, AssetValue as c from myAssets.qvd (qvd);
Here is a more complicated example from the Sales Compass example QlikView document. It shows loads from the system date and a QVD file with links to other QVD files being loaded in other sections of the script, previously declared variables, and load date variables. The opening LinkTable: declaration creates a new table from the data loaded from the other sources so that it can be handled more quickly by QlikView. This is known as a Resident Table. The declaration must precede any statements loading the table, and the load section ends with the Resident statement:
LinkTable:
LOAD DISTINCT
Year(Date) as Year,
Month(Date) as Month,
Date(MonthStart(Date), 'MMMYY') as MonthYear,
if(monthstart(Date) <= $(vTodaysDate), 1, 0) as _History,
'Q' & Ceil(Month(Date)/3) as Quarter,
Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), Year(Date) & Ceil(Month(Date)/3)) as QtrYear,
Week(Date) as Week,
Weekstart(Date) as Weekstart,
Weekend(Date) as Weekend
;
Load
Date($(vEndDate) - RecNo() +1) as DateAutoGenerate($(NumberOfDays));
JOIN LOAD DISTINCT
text([Customer Number]) as [Customer Number]
FROM ..DatabaseQVDsCustomers.qvd (qvd);
JOIN LOAD DISTINCT[Address Number] as [Customer Number], Year([Invoice Date]) as Year,[Sales Rep Number]RESIDENT Sales;
DROP FIELD [Sales Rep Number] FROM Sales;
LEFT JOIN LOAD
MonthYear,
[Customer Number],
date(MonthYear) & '_' & [Customer Number] as BudgetKey,date(MonthYear) & '_' & [Customer Number] & '_' & [Sales Rep Number] as SalesKey,date(MonthYear) & '_' & [Sales Rep Number] as QuotaKey
RESIDENT LinkTable;We can load from XML format files, such as an exchange rate sample file, from http://www.ex.com. Ex.com is one of many companies specializing in supplying foreign exchange rates on a periodic basis (hourly, daily, or monthly) for a fee. An XML load script will look similar to the following:
// Start of [sample-xml-usd.xml] LOAD statements header: LOAD hname, hvalue FROM [C:TESTsample-xml-usd.xml] (XmlSimple, Table is [xe-datafeed/header]); currency: LOAD csymbol, cname, crate, cinverse FROM [C:Testsample-xml-usd.xml] (XmlSimple, Table is [xe-datafeed/currency]); // End of [sample-xml-usd.xml] LOAD statements
HTML web files can be loaded from websites to add information to a QlikView document. An HTML script will look similar to the following script. Notice how we are able to name our incoming data columns and do mathematical changes to the incoming data in the body of the script:
// Example of loading from an HTML type file. The ranks in this file need to be reversed as they are an average of the other fields. Original data source – Reader's Digest magazine on line. LOAD upper(F1) as City, F2 as Air, F3 as Water, F4 as Toxics, F5 as Hazard_Waste, F6 as Sanitation, F7*(-1)+51 as Rank, F2*(-1)+51 as Air_Quality, F3*(-1)+51 as Water_Quality FROM [C:TEST50 Cleanest Cities in America Reader's Digest.mht] (html, codepage is 1252, embedded labels, table is @1);
In addition to loading HTML files through the file interface, you can also use the Web Files button to connect to and load a file from the Internet.
Figure 4-4: Web files loading interface
A web document-generated load script will look similar to the following example:
LOAD [e-mail us at [email protected]] FROM [http://www.blackwood.org] (html, codepage is 1252, embedded labels, table is @1, filters(ColSplit(1, IntArray())));
We already know from earlier chapters that we can load from Excel, but the different versions will look slightly different in the scripting. For example, when loading from an Excel 2010 file, two different sheets are used with similar data. Rank is set as a measure, and City is converted to uppercase and is a dimension in the following script example.
State is also a dimension. Because City and State are named the same in the two worksheets, they automatically join:
LOAD Rank,
UPPER(City) as City,
State,
Hospital_Quality as Health_Care
FROM
C:TESTNaturalDisasterRank.xlsx
(ooxml, embedded labels, table is HealthCare);
LOAD Rank,
Upper(City) as City,
Average,
Water_Quality,
[Heat stress],
[Natural disaster risk] as Low_Natural_Hazard,
State
FROM
C:TESTNaturalDisasterRank.xlsx
(ooxml, embedded labels, table is NAT_DIS);
// Loading from an Excel 2003 file
LOAD UPPER(City) as City,
State,
Zipcode,
KnowSomeone as Near_Friends,
1 as Rank
FROM
[C:TESTCities Personal List1.xls]
(biff, embedded labels, table is Personal$);Loading from an Excel 2003 file will use the file extension .xls instead of the newer .xlsx file extension. It is labeled as a biff internal format instead of ooxml. Notice that internal comments can be added to scripts with the double forward slashes that you see in front of the statement: Loading from an Excel 2003 file.
We have not exhausted the possible sources for loading to a QlikView document. Any database that we can connect to with an ODBC connection, an OLE DB connection, or a QlikView server connection can be a source to load QlikView from. It is also possible to use File Transfer Protocol (FTP) to extract the data from a file to one of the text or delimited file formats.
Figure 4-5: Database connection types
Next, we have two examples of what the script looks like when loading from ODBC data sources. The first is a connection to an SQL server (which happens to be named SQL Server), and the second is a connection to a Microsoft Access database:
// Loading from a SQL Server database ODBC CONNECT TO SQL_Server; LOAD "Avg_Ticket_Price", UPPER (City) as City, "Online_Tickets", Rank, Screens, State, Theaters, Rank as Entertainment; SQL SELECT "Avg_Ticket_Price", City, "Online_Tickets", Rank, Screens, State, Theaters, Rank as Entertainment FROM AdventureWorks.dbo."US_Theaters"; // Loading from an Access database via ODBC ODBC CONNECT TO [MS Access Database;DBQ=C:TESTgreatplaces.mdb]; LOAD DISTINCT UPPER (City) as City, ID, State, Rank, Zip; SQL SELECT City, ID, State, Rank, Zip, 1 as Cost_of_Living FROM `MLS_List`;