Transcription
QlikView DeveloperAugust 2008 ReleaseQlikView Version: 8.5 English
Copyright 2008 QlikTech International AB, Sweden.Under international copyright laws, neither the documentation nor the software maybe copied, photocopied, reproduced, translated or reduced to any electronic mediumor machine-readable form, in whole or in part, without the prior written permission ofQlikTech International AB, except in the manner described in the software agreement.Qlik View and QlikTech are registered trademarks of QlikTech International AB.Microsoft, MS-DOS, Windows, Windows NT, 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 AppleComputer, Inc.Release Date: August 2008
CONTENT1INTRODUCTION. 71.1 Who is QlikTech? .71.2 What is QlikView? .71.3 QlikView Developer .81.4 Course Logistics .101.5 Notes .112THE QLIK WHOLESALE TRADE (QWT) BUSINESS INTELLIGENCE PROJECTPLAN 132.1 Project plan review .133A SHORT INTRODUCTION TO DATA STRUCTURES. 153.1 Relational databases .153.2 Other data structures .164QLIKVIEW DATA STRUCTURES. 194.1 Comparing database structures to QlikView data structures .194.2 Data structures in QlikView .205LOADING DATA INTO QLIKVIEW. 235.1 Script editing .235.2 Edit Script Toolbar .245.3 Edit Script Menu Commands .255.4 Statements Area . 265.5 Edit Script Tool Pane .265.6 Syntax .305.7 Renaming a Field .336DATA SOURCE FILES. 356.1 The QWT primary data source .356.2 The QWT secondary data files .367CREATING THE SCRIPT. 377.1 Script generation .377.2 Creating a connection to the database .377.3 Reading tables into QlikView .408EXERCISES. 478.1 Script Debugging .489STRUCTURING THE SCRIPT. 519.1 Creating tabs in the script .513
10DATA STRUCTURE OF THE LOADED DATA. 5710.1 The Table Viewer .5710.2 System fields .5910.3 The system tab .5910.4 The system table .6010.5 Document Properties: Tables page .6111SCRIPTING CONSIDERATIONS. 6311.1 Reading the Shipments table .6311.2 Synthetic key tables .6411.3 Circular references .6611.4 Causes of circular references .6711.5 Loosely Coupled Tables .6912ADDING TEXT DATA. 7112.1 Employees .7112.2 Offices .7212.3 Script generation using the Table Files Wizard .7213LOADING AN XML FILE. 7713.1 Loading a text file in XML Format .7713.2 Renaming fields using the Qualify statement .7914KEY FIELDS14.114.214.314.414.51516EXERCISES. 87GENERATING DATA IN THE QLIKVIEW SCRIPT16.116.216.316.416.517. 83Example predicament .83How does this affect you? .83Loading a field into a table multiple times .84Using a record counter on key fields .84Does the chart really show what I want it to? .85. 89Resident Load .89Advanced – Using Orders to determine Sales Person. .91Creating data using Load Inline and Autogenerate .92Inline tables .92Autogenerate tables .98MAPPING TABLES. 9917.1 Mapping Quarters to the Orders table .9917.2 MonthYear .10217.3 Cleaning up the table structure .103184EXERCISES.107
19CREATING A CALENDAR.10919.1 Getting the Highest and Lowest date from the Orders table .10919.2 Creating variables in the script .11019.3 Creating the Master Calendar .11220INCLUDE.11521READING BUDGET INTO QLIKVIEW.11721.1 Reading Cross Tables .11721.2 Adding an Input Field .11922ADVANCED SCRIPTING.12122.1 Condition on a field in a table .12122.2 Aggregating Data .12222.3 Joining tables .12222.4 Concatenation .12322.5 Preceding Load on Preceding Load .1272324EXERCISES.129QLIKVIEW DATA (QVD) FILES.13124.1 QVD file format .13124.2 Use of QVD files .13124.3 Creating QVD files .13224.4 Manual creation of a QVD file in the script .13224.5 Automatic Creation of a QVD file in the script .13424.6 QVD file script functions .13625 NEW IN QLIKVIEW 8.5 .13725.1 Set Analysis .13725.2 Dollar-Sign Expansion .14125.3 Set Analysis / Dollar-Sign Expansion Exercise .14225.4 Hierarchy Resolution .14725.5 Hierarchy Resolution Exercise .15126QLIKVIEW SECURITY.15326.1 Access control .15326.2 Access levels .15326.3 Access control database .15326.4 Inherited access restrictions .15526.5 Hidden script .15626.6 Adding Section Access .15626.7 Access control for certain commands .15826.8 Further access control .16026.9 Unattended Command Line Reload Considerations .1615
26.10 Access restrictions on selected field values .16126.11 Field value limitation in Section Access .1622728REPORTING BUGS IN QLIKVIEW.165QLIKVIEW REFERENCE MATERIALS.167APPENDIXDATA TYPES IN QLIKVIEWA.1A.2A.3A.4.171Data storage in QlikView .171Data containing information on data type .171Data without information on data type .172Dates and times .173THE FINAL SCRIPT .1756
INTRODUCTION1 INTRODUCTIONThis chapter introduces QlikTech, and explains the differences between the QlikViewproducts. It outlines the basic capabilities of QlikView Developer and the underlyingcontents of a QlikView file. It also lays out the format and structure of the rest of themanual, and guides the student through the installation of QlikView Developer andthe course materials on their computer, setting up the interactivity and hands-onaccess required for remainder of the class.1.1 Who is QlikTech?QlikTech was founded in Lund, Sweden in 1993. Today, research and developmentcontinue in Lund, and Radnor, Pennsylvania is both U.S. and International Headquarters. QlikTech has offices and partners around the world and is experiencing rapidand sustained growth.Information is the lifeblood of any organization. It is the foundation of knowledge,and knowledge is the basis for appropriate action. This can be a distinct competitiveadvantage. QlikTech delivers fast, powerful and affordable data analysis and reporting solutions, giving users clear insight and enhanced decision-making capabilitiesacross the enterprise.How does this happen? Through innovative technologies and unmatched customerservice.1.2 What is QlikView?QlikView is a revolutionary platform that simplifies analysis for everyone. It is userfriendly and provides superfast in-memory analysis capabilities by dynamically integrating and presenting data from multiple data sources, or a single Excel or text file.QlikView provides analysis and rep
QlikView Professional is the designer’s toolkit for creating compelling QlikView lay-outs and design. This functionality is also available in QlikView Developer. QlikView manages information like the human brain works. Just like the human brain, QlikView makes associative connections with the information being pro-cessed. You – not the .
