Creating a Document in QlikView

Steps for Creating a Document in QlikView

1. LOADING DATA INTO QLIKVIEW

A QlikView document is created by retrieving data from a source, e.g. from a relational database or from text files containing data tables. This retrieval is done by writing and executing a script, in which the database, the tables and the fields to be retrieved are specified. The script can be generated automatically with the tools included in QlikView.

Looking at a delimited text file

You’ll start by looking at a csv file in a text editor:

Start a text editor, e.g. Notepad (found in the Windows Accessories group).
Open the file csv from the ..\TutorialFiles\Application\Examples directory (choose All Files in the Files of Type box).
Close the text editor.
Comma separated value files and text files with other delimiters, such as tab or semicolon, can often be imported to, and exported from, spreadsheet programs.

Loading the text file

Start QlikView (see the Basics chapter if you have forgotten).
Choose New from the File menu or from the toolbar. The next thing to do is to create a script that specifies the files to load:
Choose Edit Script from the File menu or from the toolbar. Choose Table Files on the Data tab. This opens the Open Local Files dialog box, in which you can browse for the file you wish to load. Make sure that the control Files of Type is set to Table Files. Find the file csv (the one you opened in the text editor before), select it and choose Open. The file is now opened in the file wizard, which interprets the contents of the file and helps you to load the data into the script in a correct way. Since the program has made a correct interpretation of the file, you can click Finish.

A script similar to the one below has been generated in the Edit Script dialog:

Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from c:\qlikview\tutorialfiles\
application\examples\country1.csv
(ansi, txt, delimiter is ',', embedded labels);

Study the script. Note that the words set, load and from are highlighted. This Choose Run. The data is now loaded into QlikView, and a dialog box in which it is possible to select the fields to be displayed is opened. Select the fields Area (km.sq.), Capital, Currency and Population(mio) by CTRL-clicking their names, then choose Add> to include them in the list of displayed fields. Another possibility is to double-click the field names. This will immediately put them in the list of displayed fields. The field names starting by “$” are system fields (they are only shown if the check box Show System Fields is selected).
Choose OK to close the dialog. All the fields in the column Displayed Fields will be displayed as list boxes on the active QlikView sheet.
Click on a capital, and you will find e.g. the currency used in that city.

Clear your selections.

2. ASSOCIATING DATA FROM MANY TABLES

Associations

If you have two tables listing different things, e.g. if you have one list of customers and one list of invoices, and the two tables have a field (column) in common, e.g. the customer number, this usually means that there is a relationship between the two tables.
If such a relationship exists, associations are made between the fields that are common to the tables: QlikView assumes that the two fields are one and the same thing, and the two fields are treated as one. Such a field connecting two or more tables is called a key
There are two basic rules for associations:

For two fields to be associated, they need to have the exact same name (case sensitive). Thus, e.g., Name and name are not the same and are not associated.
If a certain field has the exact same value in several different input tables, QlikView will treat it as one value and also assume that the records (rows) containing the value should be associated. For two field values to be associated, they either need to
- have exactly the same spelling (case sensitive), or
- have exactly the same numeric value

Loading and associating a second table

The new table is found in an Excel file, but you can load it in the same easy way as a text file.

Choose Edit Script from the File menu or from the toolbar.
Place the cursor at the end of the script.
Choose Table files.
Select xls from the …Tutorial\Application\Examples directory and choose Open.
Note that Excel is set as type this time, and that the Table box contains the name of the worksheet. This Excel document contains only one worksheet; if there had been several sheets or named tables, the Table box would have given you the possibility of choosing from which one of them data should be retrieved. Click Finish. Your script will now look similar to the one below (to improve clarity, we will from now on show relative paths, since the directory will stay the same):

 Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country1.csv (ansi, txt, delimiter is
',',embedded labels);
Load [Customer ID],
Customer,
Address,
City,
Zip,
Country
from customer.xls (ansi, biff, embedded labels, table
is [Customer$]);

Choose Run.
The dialog in which you choose the fields to display now appears. All fields, except the field Country from the file xls have been added to the column of available fields. The field Country is shown in the list of displayed fields. The field Country has been associated with the previously loaded fields with the same fieldname.
Add the field Customer to the column of displayed fields.
Choose OK.
Save your document.
Click Alma-Ata, the capital of Kazakstan, and note that the fictive company has two customers in Kazakstan.
Clear your selections.

Renaming fields

The directory contains additional files (tables) that are relevant to your document. Suppose you want to associate the file Transact.csv, a file containing information about transactions, sales, etc. concerning the customers in the document:

Choose Edit Script from the File menu or from the toolbar.
Place the cursor at the end of the script.
Choose Table Files.
Select csv and choose Open. This opens the file wizard
Make sure that Delimited is set as type, Comma as delimiter and that the check box Embedded Labels is selected.
The file wizard provides excellent possibilities for renaming fields. Simply click in the table header of ID Customer, then type the new name, Customer ID. Make sure not to forget the space between the words: any misspelling prevents QlikView from interpreting the fields as being one and the same.
Press ENTER. The name of the field has been changed.
Click Finish.

The automatically generated script looks similar to the one below:

 Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country1.csv (ansi, txt, delimiter is ',',
embedded labels);
Load [Customer ID],
Customer,
Address,
City,
Zip,
Country,
from customer.xls (ansi, biff, embedded labels, table
is [Customer$]);
Load [Transaction ID],
Year,
Month,
Day,
[Salesman ID],
[Product ID],
[SerialNo],
[ID Customer] as [Customer ID],
[List Price],
Sales,
[Gross Margin]
from transact.csv (ansi, txt, delimiter is ',',
embedded labels);

Note the line [ID Customer] as [Customer ID]: it has appeared as a result of the change you made in the file wizard, and means that the field ID Customer will be loaded into QlikView with the name Customer ID (thus assuring the necessary association).

Choose Run.
The Fields page of the Sheet Properties dialog appears. Add a field from the file csv to the column of displayed fields, e.g. Sales.
Click OK.
Save your document.
Select Finland in the list box Country. The program immediately provides the geographical data stored in the country tables – but also displays the names of the customers residing in Finland, as well as the sales values related to them.
Clear your selections.

3. CONCATENATING TABLES

However, instead of being associated, tables can also be merged. If two input tables are lists of the same thing, but contain different values, e.g. if one is a list of countries in Europe and the other one a list of countries in North and South America, the second table can be seen as a continuation of the first. The tables should then be concatenated.

Automatic concatenation

If two tables that have exactly the same set of fields are entered, QlikView automatically treats the second table as a continuation of the first. This is called concatenation of tables. Any number of tables can be concatenated into one table.
Your QlikView document retrieved data from a file with a limited number of countries. The Tutorial\Application\Examples directory contains a second file listing countries, whose field names correspond exactly to those of the already loaded Country1.csv. When you load the second file, the two tables will be automatically concatenated.

Choose Edit Script from the File menu or from the toolbar.
Position the cursor after the load statement loading the file csv (all statements end with a
semicolon) and press ENTER to get an empty row. The order of the load statements is arbitrary, but you get a better overview of your script by keeping the country files together.
Click Table Files.
Select csv, then choose Open. This opens the file wizard
Make sure that Delimited is set as type, Comma as delimiter and that the check box Embedded Labels is selected.
You don’t need to rename any fields this time, so click Finish. You should now have a script similar to the following:

Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country1.csv (ansi, txt, delimiter is ',',
embedded labels);
Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country2.csv (ansi, txt, delimiter is ',',
embedded labels);
Load [Customer ID],
Customer,
Address,
City,
Zip,
Country,
from customer.xls (ansi, biff, embedded labels,
table is [Customer$]);
Load [Transaction ID],
Year,
Month,
Day,
[Salesman ID],
[Product ID],
[Serial No],
[ID Customer] as [Customer ID],
[List Price],
Sales,
Gross Margin
from transact.csv (ansi, txt, delimiter is ',',
embedded labels);

Choose Run. The Fields page in the Sheet Properties dialog box is opened. The fields you selected last time are already in the column of displayed fields. No new fields have appeared in the list of available fields, since the field values of csv have been added to the corresponding fields of Country1.csv.
Choose OK to close the dialog.
Save your document.

Forced concatenation

Sometimes you want to concatenate tables also when they have different sets of fields. QlikView will then not automatically concatenate the two tables: you need to use the concatenate statement, which concatenates a table with the last created logical table.
The values of the missing fields in the concatenated table will be NULL, i.e. Qlik-
View will treat these fields as having no value.
Do the following:

Choose Edit Script from the File menu or from the toolbar.
Position the cursor after the statement loading csv. This time the order of the statements is not arbitrary, since the concatenate statement forces concatenation with the last created logical table in the script.
Choose Table Files.
Select csv and choose Open. This opens the file wizard
Make sure that the wizard has made a correct interpretation, then click Finish.

This generates a script similar to the one below:

Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country1.csv (ansi, txt, delimiter is ',',
embedded labels);
Load Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country]
from country2.csv (ansi, txt, delimiter is ',',
embedded labels);
Load Country,
[Official name of Country],
[Area(km.sq)]
from country3.csv (ansi, txt, delimiter is ',',
embedded labels);
Load [Customer ID],
Customer,
Address,
City,
Zip,
Country,
from customer.xls (ansi, biff, embedded labels, table
is [Customer$]);
Load [Transaction ID],
Year,
Month,
Day,
[Salesman ID],
[Product ID],
[Serial No],
[ID Customer] as [Customer ID],
[List Price],
Sales,
Gross Margin
from transact.csv (ansi, txt, delimiter is ',',
embedded labels);

Position the cursor in front of the load statement that loads Country3.csv and type Concatenate. If the spelling is correct, the word concatenate will turn blue just like load and from etc., since it is also a keyword. Make sure there is a space between the two words:
Concatenate Load Country,
Official name of Country],
[Area(km.sq)],
from country3.csv (ansi, txt, delimiter is ‘,’,
embedded labels);
…

Choose Run.
Click the OK
Select the country Seychelles.
Clear your selections.

To get a clear picture of the contents of the concatenated table, create a table box containing the fields of the country files, i.e. Country, Capital, Area (km.sq), Population(mio), Pop.Growth, Currency, Inflation, Official name of Country. Use the scroll bar to browse through the data of your table box. You’ll note
that some of the rows are not complete, but contain a ’-’ instead of a value. This is the case for all the countries from the third country file, containing only a subset of the fields: the values of the missing fields are treated as NULL.

Using the Table Viewer

Choose Table Viewer from the File
Click on the header of the table Country1. All tables directly associated with this table (only one actually) will be highlighted.
Click on the field Customer ID in one of the tables where it appears. Notice that the field name will be highlighted in all tables where it appears.
Close the Table Viewer by clicking OK.

4. LINKING EXTERNAL INFORMATION TO A DOCUMENT

The links are defined in information tables which must be loaded in a special way. In this lesson you’ll link flags to specific values in the Country list box.

Looking at an info file

Open a text editor, e.g. Notepad, and choose Open from the File
In the box Files of type, select All files.
Open the file Flagsoecd.csv in the …\Tutorial\Application\Examples directory.
Close the editor.

Loading the info file

The next step is to load the info file into QlikView.

Choose Edit Script, and click Table Files.
Select the file csv and click Open. This opens the file wizard.
Like for the files already loaded, Delimited is set as type, Comma as delimiter.
Embedded Labels is selected as label.
Click Finish.
Change the script by manually adding the word info before the load
The word info being a keyword in the script, it will turn blue. The script should look similar to the following:

...
Load [Transaction ID],
Year,
Month,
Day,
[Salesman ID],
[Product ID],
[Serial No],
[ID Customer] as [Customer ID],
[List Price],
Sales,
Gross Margin
from transact.csv (ansi, txt, delimiter is ',',
embedded labels);
Info Load Country,
I
from flagsoecd.csv (ansi, txt, delimiter is ',', embedded labels);

Choose Run.
The Fields page is opened. No new fields have been added. Click OK.
Save your application.

Viewing the linked information

Select Germany from the list of countries.
A small info symbol appears in the upper right-hand corner of the list box. Click it.
Close the window
Select France from the list of countries.
Click the info symbol to make the specified picture appear
Close the flag and clear all selections.

Embedding external info

If you want to be able to send a QlikView document to other people without worrying about sending the picture files as well, there is an option to embed the info in the QlikView file.

Choose Edit Script
Find the statement which starts by info load.
Precede that statement with the word bundle, so that it now starts bundle info load.
Run the script.

5. LOADING ADDITIONAL FILES

Loading a tab separated file without labels

The loading procedure is similar to the one you’ve encountered in the previous lessons:

Go to the Sales
Choose Edit Script from the File menu or from the toolbar.
Position the cursor at the end of the script.
Click Table Files.
Select tab, then choose Open. This opens the Table wizard (see above).
Delimited is still set as type, but this time Tab is selected as delimiter. Furthermore, Labels should be set to None because the first row contains ordinary field values, not field names. Change Labels to None in the drop-down list if does not already have this setting.
Click in the header of the first column where it says @1. Type Market and press ENTER. Click in the header of the second column where it says @2. Type Country and press ENTER
Click Finish. Your script now looks similar to the one below:

...
Info Load Country,
I
from infofile.csv (ansi, txt, delimiter is ',',
embedded labels);
Load @1 as Market,
@2 as Country
from markets.tab (ansi, txt, delimiter is '\t', no
labels);

Click Run to execute the script.
Move the new field Market to the column of displayed fields, then click OK.

Loading a file via ODBC

Open the Edit Script
Instead of clicking Table Files, you must now click the Connect button to establish a connection with the data source.
The Data Link Properties dialog opens. Make sure that the OLE DB Provider for ODBC Drivers is selected, then click Next to get to the Connection Since you are working with a generic data source not yet defined, select the option Use connection string, then click the Build button.
Go to the tab Machine Data Source.
Select MS Access Database, then click OK.
From the Login dialog that opens, click the .. button.
Browse for the file. Once you find the correct location, the salesman file should be the only one available in the left list. Select it, then click OK to close the dialog.
Click OK to close the remaining dialogs.
Click the Select

By default, a star is selected in the SELECT group. The star is equivalent to all fields. You want to load all fields, but for consistency reasons you’ll select them to make their names appear in the script:

Click on the field Salesman ID, then press the SHIFT key and hold it down while clicking the last field name. Verify that all the fields are selected by studying the preview.
Click OK. Your script should now look similar to the following:

...
Load @1 as Market,
@2 as Country
from markets.tab (ansi, txt, delimiter is '\t', no
labels);
CONNECT TO 'MS Access Database;
DBQ=c:\QlikView\TutorialFiles\Application\
Examples\SALESMAN.mdb;';
SQL SELECT `Salesman ID`, Salesman, `Distributor ID`
FROM SALESMAN;

The salesman table is associated with the existing data via the field Salesman ID, which it has in common with Transact.csv.