Build your first QlikView e-Learning
Exercise Instructions
2010-11-11
QlikView Version:11.xx English
1996-2011 QlikTech International
Copyright © 2011 QlikTech International AB, Sweden.
Under international copyright laws, neither the documentation nor the software may
be copied, photocopied, reproduced, translated or reduced to any electronic medium
or machine-readable form, in whole or in part, without the prior written permission of
QlikTech International AB, except in the manner described in the software agreement.
Qlik®View is a registered trademark of QlikTech International AB. In the United States
of America and Canada, Qlik®View is a registered trademark of Qlik®Tech , Inc .
Microsoft, MS-DOS, Windows, Windows NT, Windows 95, Windows 98, Windows
ME, Windows 2000 Windows XP, Windows Vista, SQL Server, FoxPro, Excel, Access,
ActiveX, the Internet Explorer logo, and MS Query are trademarks of Microsoft
Corporation.
IBM, AS/400 and PowerPC are trademarks of International Business Machines
Corporation.
Borland, Paradox and dBASE are trademarks of Borland International.
ORACLE and SQL*Net are trademarks of Oracle Corporation.
Apple, TimeType, Macintosh, PowerMacintosh and MacOS are trademarks of Apple
Computer, Inc.
1996-2011 QlikTech International
Requirments
General Sales Trend
Top selling Products and Customers
Sales and Margin Performance
Exercise – Extract the data
Create a New QlikView document and save it
Access the Script Editor and load the SALESDATA.xls using the Table File
Wizard
Rename the table in the script
Put the cursor in on an empty line in the script and load the CUSTOMER.xls
Rename the table in the script
Put the cursor in on an empty line in the script and load the DIVISION.xls
Rename the table in the script
Put the cursor in on an empty line in the script and load the REGIONS.xls
Rename the table in the script
Put the cursor in on an empty line in the script and load the
CUSTOMERADDRESS.xls
Rename the table in the script
Save and Reload
1996-2011 QlikTech International
Exercise - Transform
Access the Table Viewer to check the current data model. Note that the
Salesdata table is currently not linked
Access the Script Editor
Link the Sales and Customer Tables
o Locate the field [Customer Number] in the Customer table and copy the
name
o Locate the field CustKey field in the Salesdata table and rename it
Customer Number
o CustKey AS [Customer Number],
Create the Year and Month fields based on the [Invoice Date] using the Year and
Month functions
o Copy the [Invoice Date] in the Salesdata table
o On a new line in the script type:
o Year([Invoice Date]) as Year
o Month([Invoice Date]) as Month
Limit the data to be loaded to where a Customer Number exists
o Go to the Customer Table in the script
o Before the semicolon in the FROM statement type:
where exists ([Customer Number]);
Save and Reload
1996-2011 QlikTech International
Exercises - Interface
Exercise – Listboxes as Calendar
Right click in sheet and select option Select Fields
Select Year and Month
Access Properties - Presentation and uncheck the single column option for both
listboxes
De-select Show Caption on the Captions tab
Resize and place the two listboxes across the top of the sheet
Exercise – Current Selection Object
Right click in sheet and select New Sheet object - option Current Selection
Make sure Drop down select is ticked on General tab before clicking ok
Resize and place the object on the sheet
Exercise - Listboxes and Multibox Object
Add the fields: Division Name, Region Name and Customer to the sheet
Resize and place them on the left-hand side
Right click in sheet and select New Sheet object - option Multibox
Add fields Invoice Date and Invoice Number
Resize and place the Multibox object on the sheet
Exercise – Line chart
Right click in sheet and select New Sheet object - option Chart
Change the title in Chart to - ‘Sales Trend’
Select Line chart
Add the field Year as dimension
Add the expression - Sum([Sales Amount])
Change number formatting to Money
Click Finish
Exercise – Bar chart
Clone the line chart and access the properties of the clone you just created
Change the chart type to bar chart
Change the title of the chart to ‘Top 5 Customers/Products’
Remove Year as dimension
Add a New Group by clicking Edit Groups
Give the Group a name and select Cyclic Group
Add fields Customer and Item to the Group
Change the label for the field Item to Products and click ok
Add the group as a dimension in the chart
Go to Sort and select Sort by Y-value
1996-2011 QlikTech International
Go to style and change the orientation of the chart to lying bars
Go to Dimension Limits and limit the number displayed to 5 largest, deselect
Show Others and click ok
Exercise – Container Object
Right click in sheet and select New Sheet object - option Container
Add the two charts
Change presentation to Grid
Resize and place the Container object on the sheet
Remove the two charts
1996-2011 QlikTech International
