Advanced Features of QlikView

List of QlikView Advanced Features

1. MORE ABOUT ASSOCIATIONS

Creating a system sheet

Start QlikView.
Choose Open from the File
Select the file qvw found in the Advanced folder, then click Open.
Choose Add Sheet… from the Layout
Name the sheet System, then click Next>.
Move the system fields (the ones preceded by a dollar sign, $) to the column of displayed fields.

The system field’s show

the names of the fields retrieved ($Field),
the names of the tables loaded ($Table),
the number of rows and columns in a table ($Rows and $Fields),
the column number for a specific field ($FieldNo), and
the names of the info tables loaded ($Info).

Click Finish.
Size the list boxes until you see all the field names and all the field values, then rearrange the boxes.

Using the system sheet

Your system sheet is now ready, but to further improve it, chooses frequency display for the list box $Field:

Open the Properties dialog and go to the General
Select the check box Show in the Frequency group, and then go to the Sort
Select Frequency, Descending, and then click OK.

Click Country in the $Field
As soon as only one table or info table is possible (selected or optional) in a list box, a small info symbol is displayed in the top right-hand corner of the list box.
Clicking this will allow you to edit the table directly:
Click one of the optional tables in the list box $Table.
The info symbol appears in the top right-hand corner. Click it
The original table is now opened by the associated program. Study it, then close the program to get back to QlikView.
Clear your selections.

Creating a system table

Besides displaying the system fields in list boxes, you can also illustrate the relations by creating a system table:
Right-click on the sheet and open the cascade menu New Sheet Object where you select System Table.

Showing frequency in associating fields

Suppose you work with the customer sheet and want to see how many customers you have in different countries, i.e. how many times the countries occur in the data.

Go to the Customer sheet of your document.
Click on the list box Country with the right mouse button, then click Properties.
Go to the General
Close the List Box Properties
Open the Edit Script
Find the statement loading csv and position the cursor after the last field (Country), then type “, Country as CustomerCountry“. The load statement now looks similar to the following:

Load [Customer ID],
Customer,
Address,
City,
Zip,
Country, Country as CustomerCountry
from c:\qlikview\tutorialfiles\application\
examples\customer.csv(ansi, txt, delimiter is ',', embedded labels, header
is 0);

You need to keep the field Country, otherwise, there will be no key field and thus no association with previously loaded tables.
Click Run.
Move the new field CustomerCountry to the list of displayed fields, and then click OK.
Click on the CustomerCountry list box with the right mouse button, then choose Properties.
On the General page, select the check box Show in the Frequency group, then go to the Sort
Select Frequency, and then click OK.
Remove the list box Country.
Adjust the layout.
Save the file as qvw.
Close the file. If you wish, compare it with the file qvw found in the Advanced folder.
If you won’t be working with QlikView for a while, you can now exit the program.

2. LOAD INLINE

In some cases, you may want to add data by typing it directly in the script rather than loading it from a file or a database. In this lesson, you’ll learn how to do this with load inline.
Load inline can also be used for mapping (associating new information with already existing information via a field name), which constitutes the second section of this lesson.

Adding a record with load inline

Start QlikView and open the file qvw found in the …Tutorial\ Advanced directory
Open the Edit Script
Position the cursor at the end of the script, then type the following lines:

Load * Inline [
Customer ID,Customer, Address, City, Zip, Country
1181, Alexander’s Catering Service, Fisherman’s Drive
4, Portsmouth, BH 354 RW, Great Britain];

Choose Run.
Choose OK to close the dialog.
No new field has been added, but there are new field values in some of the list boxes. Click Alexander’s Catering Service in the customer list and study the result.

Mapping data with load inline

Data mapping means associating new information to already existing information via a field name. Typical examples of data mapping could be connecting an account number to an account name or splitting a date into three fields for year, month and day, respectively. The data mapping can be done using an external file or straight in the script using load inline.

Open the Edit Script
Position the cursor at the end of the script.
Click on Inline Wizard… under Data in Script on the Data
The dialog that opens looks like a small spreadsheet and in fact works much like one. However, there is no support for formulas in the data cells.
The cursor will be positioned in the top-left data cell. Enter the 1 as shown in the picture below. Use ENTER or the arrow keys to move between cells and fill in the table as shown below.
Finally double-click in the header row over 1 and enter the field name Month. Repeat for the remaining columns as shown below.

7. Click OK and you should have a piece of script looking like this:

Load * Inline [
Month, MonthName, Quarter
1, January, 1
2, February, 1
3, March, 1
4, April, 2
5, May, 2
6, June, 2
7, July, 3
8, August, 3
9, September, 3
10, October, 4
11, November, 4
12, December, 4];

8. Choose Run.
9. Click OK.
10. Create a bar chart showing sales per quarter (choose Quarter as variable and add the expression Sum of Sales). Save the document under a name of your choice and close the file. If you wish, compare your file with the file qvw.
11. If you won’t be working with QlikView for a while, you can also exit the program.

3. FIELD GROUPS AND CYCLIC DISPLAY

Field groups

One main difference between QlikView and other database viewers, OLAP tools, etc. is that in QlikView there is no need to predefine any hierarchies in the input data. The unique associative logic of QlikView gives you the complete freedom to access any field as a full dimension in any order you like.
QlikView, therefore, offers the possibility to define groups of fields. The groups can be hierarchic (drill-down) or nonhierarchic (cyclic).

Creating a drill-down group

Start QlikView, then open the file qvw found in the …Tutorial\Advanced directory.
Choose Document Properties from the Settings menu and go to the Groups
Click the New The Group Settings dialog opens. Change the default name to Time in the Group Name box.
Select Year, Quarter and Month in the list of available fields by CTRL-clicking them, then click Add to move them to the column of used fields.
Use the Promote and Demote buttons to get the correct hierarchy: Year, Quarter, Month. This is of great importance, since the order of the fields in the group corresponds to the display order in charts.
Click OK

Creating and using a drill-down chart

Go to the Sales sheet, then click the Create chart button in the toolbar (if the design toolbar is not displayed, choose Design Toolbar from the View menu).
Choose Drill-down as window title, then click Next> to go to the Dimensions
Choose Time as a variable by moving it to the column of displayed fields.
Click Next.
The Edit Expression dialog automatically opens. Compose the expression Sum of Sales, then click Paste. Type Sales in the Label
Click OK.
Click Next> until you get to the Colors Select the checkbox Multicolored.
On the Number page, select Integer and set 1000$ as Thousand
Symbol, then click Finish.

10. Select the bar 1996 in the chart.

11. Select the bar representing the fourth quarter.

Creating a cyclic group

Any fields can be grouped together in a cyclic group.

Choose Document Properties from the Settings menu and go to the Groups
Click the New The Group Settings dialog opens. Change the default name to Cyclic in the name box.
Select Country, Salesman and Year in the list of available fields, then click Add to move them to the column of used fields. The order of the fields in the list is of no importance when defining cyclic groups.
Select the Cyclic
Click OK

Creating and using a cyclic chart

Click the Create chart button in the toolbar (if the design toolbar is not displayed, choose Design Toolbar from the View menu).
Choose Cyclic as window title, then click Next> to go to the Dimensions
Double-click the Cyclic group to move it to the column of displayed fields/groups.
Click Next>.
The Edit Expression dialog automatically opens. Compose the expression Sum of Sales, then click Paste. Type Sales in the Label
Click OK.
Click Next>. On the Sort page, sort the values by y-value.
On the Presentation page, set Max Number to 10.
Click Next> until you get to the Colors Select the check box Multicolored.
On the Number page, select Integer and set 1000$ as 1 000 Symbol, then click Finish.
Switch to the next field by clicking the cycle icon in the bottom right-hand corner of the chart. Now Salesman, the second field, is displayed. If you click the cycle icon a second time, the sum of sales per year will be shown. Year is the third and last field of the field group.
Minimize the chart.

Cyclic display of Expressions

The Expressions page in the Chart Properties dialog provides a check box named Cyclic Display. If you have a chart with two expressions, you can choose to display them sequentially instead of simultaneously.

Click the Create chart button in the toolbar (if the design toolbar is not displayed, choose Design Toolbar from the View menu).
Choose Cyclic Display as window title, then click Next> to go to the Dimensions
Move the field Year to the column of displayed fields, then click Next>.
The Edit Expression dialog automatically opens. Compose the expression Sum of Sales, then click Paste.
Click OK. Note that the Cyclic Display check box is disabled: you need two expressions to be able to use it.
Click Add again to add a second expression.
This time, create the expression Total count of Sales.
Click Paste, then OK. The Cyclic Display check box is now enabled: select it.
Click Next>. On the Sort page, sort the values by y-value, ascending.
On the Presentation page, set Max Number to 10.
Click Next> until you get to the Color Select the check box Multicolored bars.
On the Number page, select Integer and set 1000$ as 1 000 Symbol (for the first expression), then click Finish.

The chart looks like an ordinary bar chart showing the sum of sales per year:

However, the cycle icon in the bottom left-hand corner indicates that the chart has further potential.

Click the cycle icon.
The chart now turns to showing the number (total count) of sales performed during different years. Naturally, you can choose to display more than two expressions this way. It is also possible to combine groups and cyclic display into powerful multi-dimensional charts. Only your imagination will set the limits.
Save the document using a name of your choice. If you wish, compare it with the file qvw.
Close the file. If you won’t be working with QlikView for a while, you can also exit.

4. HANDLING CROSS TABLES

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. By using the QlikView crosstable statement, you can load this type of table in a very elegant way.

Loading a cross table

Close the file.
Start QlikView and choose New from the File
Open the Edit Script
Click Table Files, then find the file csv and click Open.
If the file wizard has made a correct interpretation, click Finish.

The following statement has been generated in your script:

Load Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun
from c:\qlikview\tutorialfiles\advanced\
examples\crosstable1.csv
(ansi, txt, delimiter is ',', embedded labels);

Load the file by clicking Run.
Add all the fields except the system fields to the column of displayed fields.
Click OK.

The following list boxes appear on your screen:

9. This is not what you want. Open the Edit Script dialog box again.
10. Now add the crosstable prefix, indicating that the table is to be loaded as a cross table, to your load The crosstable prefix should be followed by a parenthesis containing the names you wish to apply to the new fields:

 Crosstable (Month, Sales) Load
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun
from c:\qlikview\tutorialfiles\advanced\
examples\crosstable1.csv
(ansi, txt, delimiter is ',', embedded labels);

11. Click Run.
12. The Fields dialog page opens. Move the fields Month and Sales to the column of displayed fields (the field Year is already there), then click OK.
13. Save the document using an appropriate name, then close it.

Loading a cross table with more than one regular column

Choose New from the File
Open the Edit Script
Click Table Files, then find the file csv and click Open. We will now use a special wizard for creating the crosstable statement.
Click Next. This will take you to the last page of the file wizard, which we have not used up to this point.
Click the Crosstable
QlikView asks if Salesman is a qualifying field (regular column). Click Yes.
QlikView asks if Year is a qualifying field. Click Yes.
QlikView asks if Jan is a qualifying field. Click No.
QlikView now asks for the name of the new field that will contain the month names. Type Month and click OK.
QlikView will then finally ask for the name of the field to combine the sale figures. Type Sales and click OK.
In the preview pane you can now see the transformed table. Click Finish.

The script generated will look as follows:

CROSSTABLE(Month, Sales, 2)
Load Salesman,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun
FROM
g:\documentation\qv\5.0\manual\eng\tutorialfiles\advanced\examples\crosstable2.csv (ansi, txt, delimiter is',', embedded labels);

Click Run.
The Fields dialog page opens. Move the fields Salesman, Year, Month and Sales to the column of displayed fields, then click OK.
Save the document using an appropriate name, then close it. If you won’t be working with QlikView for a while, you can also exit the program.

5. AND-MODE IN A LIST BOX

Two selections in different list boxes are always interpreted as logical and, i.e. Qlik- View will show all the field values associated with both the selections. A multiple selections within a list box is however usually interpreted as logical or, i.e. QlikView will show data entries associated with any of the selected values.

Making an and-selection

In the file And.qvw in the …Tutorial\Advanced directory, there is such a list box:

Start QlikView.
Open the file qvw by pressing the Open button in the toolbar, browsing for the file and clicking Open.
Choose the tab Geography, and find the list box Membership
Click on the list box with the right mouse button, then choose Properties, General.
Mark the check box And mode, then choose the OK
Select Europe by clicking it.
Select G-7 by CTRL-clicking.

Making a not-selection

It is also possible to exclude countries in a similar way:

Deselect G-7 by CTRL-clicking it.
Select G-7 by CTRL-clicking it, keeping the mouse button down. Release the button when the cell has turned red.

Close the file. If you won’t be working with Qlik-View for a while, you can also exit the program.

Characteristics of the and-table

Not every field can be set to logical and mode. The and mode is only possible if the field corresponds to the second column of a two-column table.
Also, the field must not be fetched from more than one table. The reason for this is that the and-alternative is logically meaningful only if the concerned field is associated to only one other field. Finally, there must be no duplicate records in the table. It is thus advisable to load this kind of table using the distinct predicate. If the field is loaded this way, the And mode control in the List Box Properties dialog will no longer be dimmed and the logical model of the list box can be changed.

6. SEMANTIC LINKS

Working with semantic links

Start QlikView.
Open the file qvw in the …Tutorial\Advanced directory.
Select Eisenhower.

You see that he was the 34th president. But who was his predecessor?

Click on Predecessor in the Relation list box.

You immediately find that it was Harry S Truman. Note that Predecessor does not turn green. Instead, 33 in the No list box turns green. By clicking on Predecessor you make an indirect selection of an object, other than the one that is currently selected. Predecessor and Successor are typical semantic links.

Close the file.

Loading the logical table

Click the New button to create an empty document.
Choose Edit Script from the File
Click Table Files.
Find the file csv in the …\TutorialFiles\Advanced\Examples directory, then click Open.
The file wizard opens. Make sure that the program has made a correct interpretation, then click Finish.
Click Run to execute the script.
Add all the fields except the system fields to your document, then click OK.

Extracting the semantic table from existing data

Open the Edit Script dialog again.
Position the cursor at the end of the script.

Type the following rows:

Load[No] -1 as No,
'Successor' as Relation,
[No],
'Predecessor' as Relation
from c:\qlikview\tutorialfiles\advanced\
examples\presdnts.csv
(ansi, txt, delimiter is ',', embedded labels) where
[No] > 1;

Loading the semantic table using load…resident

Open the Edit Script
Modify the script to make it look like the one above.
Click Run. If you have done everything correctly, the script is executed and no changes occur in the document.
Save your document as qvw.
Close the file. If you won’t be working with QlikView for a while, you can also exit the program.

7. NUMBER FORMATS

Interpretation and formatting

The issue of obtaining correct number formats is really a question of two different things:
1. Interpretation of data when it is loaded
2. Display of different number-based data types

Data representation inside QlikView

The string representation is always available and is what is shown in the list boxes and the other sheet objects. Formatting of data in list boxes (number format) only affects the string representation.
The number representation is only available when the data can be interpreted as a valid number. The number representation is used for all numeric calculations and for numeric sorting.

Changing the number interpretation format

Click the New button to create a blank document.
Open the Edit Script dialog, then click Table Files.
Find the file csv, then click Open.
If the file wizard has made a correct interpretation of the contents, click Finish.
Click Run to execute the script.
Move the fields Customer, Date and Sales to the column of displayed fields, then click OK.

Formatting of data

Click on the list box Date with the right mouse button, then choose .. from the float menu.
Go to the Number
Choose Override Document Settings in order to set a separate number format for the list box.
The format is set to Mixed. Change it to Date by selecting the option.
In the Format box, the default date format of the operating system appears. It can be changed to any other format of your choice. For instance, you may prefer the standard ISO format YYYY-MM-DD. Erase the contents of the Format box and enter the new format, or click the ISO
Click OK.
Save and close the document. If you won’t be working with QlikView for a while, you can also exit the program.

8. ACCESS RESTRICTIONS

Access levels

Access to QlikView documents can be restricted to specified users or groups of users. When creating the document, these are assigned the access levels ADMIN or USER. If no access level is assigned, the user cannot open the QlikView document.

Sections in the Script

All-access control is managed via files, SQL databases or inline clauses in the same way as QlikView normally handles data. It is thus possible to store the access data in a normal database. The script statements managing the security tables are given within the access section, which in the script is initiated by the statement section access.

Loading security tables

Open the document for which you want access control, e.g. qvw.
Open the Edit Script dialog and position the cursor at the beginning of the script, but after the set
Click Table Files.
Select the files csv and accserid.csv (in the …Tutorial\Advanced\Examples directory) and click Open.
The files are opened in the file wizard. Click Finish for both files.
For the tables to be used for access control, the statements loading them need to be placed in a separate section. Type section access; at the top of the script (after the set statements). Don’t forget the semicolon.
To distinguish the access section from the application section, position the cursor after the statements loading the security tables, then type section application;. Don’t forget the semicolons: they indicate the end of a statement.
Choose Run to execute the script.
Click OK to close the dialog.

Using the Security pages

Choose Document Properties from the Settings
Go to the Security
The Security page contains a list of QlikView commands. By deselecting a
check box, you prevent the document users from executing that command.
Deselect Add Sheets and Edit Script, then click OK.
Note that the commands you deselected are now dimmed, i.e. inactive.
Save the file as qvw, then close it and exit QlikView.

Working with access restriction

Open QlikView, then choose Open from the File
Find the file qvw and click Open.
QlikView prompts for the correct User ID. Enter Pete, then click OK.
QlikView now prompts for the correct password. As Pete, you have USER rights from all the computers. Enter your password, i.e. NUMBER1 (case insensitive). Click OK.
Close the file. If you won’t be working with QlikView for a while, you can also exit the program.