QlikView 11 for Developers

QlikView 11 for Developers
eBook: $35.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 20%!
Print + free eBook + free PacktLib access to the book: $95.98    Print cover: $59.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Learn to build applications for Business Intelligence while following a practical case -- HighCloud Airlines. Each chapter develops parts of the application and it evolves throughout the book along with your own QlikView skills.
  • The code bundle for each chapter can be accessed on your local machine without having to purchase a QlikView license.
  • The hands-on approach allows you to build a QlikView application that integrates real data from several different sources and presents it in dashboards, analyses and reports.

Book Details

Language : English
Paperback : 534 pages [ 235mm x 191mm ]
Release Date : November 2012
ISBN : 1849686068
ISBN 13 : 9781849686068
Author(s) : Miguel García, Barry Harmsen
Topics and Technologies : All Books, Big Data and Business Intelligence, Data, Enterprise

Table of Contents

Chapter 1: Meet QlikView
Chapter 2: Seeing is Believing
Chapter 3: Data Sources
Chapter 4: Data Modeling
Chapter 5: Styling Up
Chapter 6: Building Dashboards
Chapter 7: Scripting
Chapter 8: Data Modeling Best Practices
Chapter 9: Basic Data Transformation
Chapter 10: Advanced Expressions
Chapter 11: Set Analysis and Point In Time Reporting
Chapter 12: Advanced Data Transformation
Chapter 13: More on Visual Design and User Experience
Chapter 14: Security
  • Chapter 1: Meet QlikView
    • What is QlikView?
      • How does QlikView differ from traditional BI?
        • Associative user experience
        • Technology
        • Adoption path
    • Exploring data with QlikView
      • Getting QlikView
      • Navigating the document
      • Slicing and dicing your data
        • List-boxes
        • Selections in charts
        • Search
        • Bookmarking selections
        • Undoing selections
      • Changing the view
        • Cyclic Groups
        • Drill down Groups
        • Containers
      • But wait, there's more!
    • The technology and components behind QlikView
      • The way the data flows
    • When QlikView use expands
      • Create content
      • Reload, publish, and distribute content
      • Consume content
  • Meet HighCloud Airlines
  • Summary
    • Chapter 2: Seeing is Believing
      • What is a SiB?
      • Preparing the workspace
        • Setting up the folder structure
        • Creating the QlikView document
      • Creating the app
        • The requirements
        • Constructing the data model
          • What is a data model?
          • Loading the fact table
          • Playing with listboxes
          • Associating additional tables
        • Creating the dashboard tab
          • Creating and positioning the filters and user controls
          • Number of flights over time
          • One chart and multiple analyses with cyclic expressions
          • Adding a time drill-down group
          • Top 10 routes
      • Summary
      • Chapter 3: Data Sources
        • Using ODBC and OLE DB drivers
          • Installing the drivers
        • Accessing custom data sources
          • Third-party custom connectors
        • Reading table files
      • Extracting data—two hands-on examples
        • Extracting data from MS Access
          • Configuring the driver
          • Creating the OLE DB connection string
          • Querying the database
          • Reloading the script
        • The resulting data model
        • Loading a table file
          • Specifying the file attributes
          • The transformation step
          • Refining the input table
        • The resulting script
      • QVD and QVX files
        • QVD files
        • QVX files
      • Loading an Inline table
      • Summary
        • Chapter 4: Data Modeling
          • Dimensional data modeling
            • Back in the day
            • Relational databases and ER modeling
            • Dimensional modeling
              • The star schema
              • The snowflake schema
              • Creating the dimensional model
            • Dimensional models in QlikView
          • The associative data model
            • Guidelines for table associations
              • How associations are created
              • Avoiding data model conflicts
          • The Table Viewer window
            • Table information
            • Field information
            • Table preview
            • Table viewer menu
          • Summary
          • Chapter 5: Styling Up
            • Design requirements
            • The Document Properties window
            • The Sheet Properties dialog
            • Setting the object properties
              • Caption colors and style
                • Changing the caption colors
                • Setting the caption font
                • Setting the content font
                • Setting the global font
              • Propagating the object appearance
              • Setting the default Sheet Object Style
              • Hiding captions
            • Working with listboxes
              • Adding listboxes
              • The List Box Properties dialog
                • The General tab
                • The Expressions tab
                • The Sort tab
                • The Presentation tab
                • The Number tab
                • The Font tab
                • The Layout tab
                • The Caption tab
            • The Multi Box
            • The Current Selections Box
              • Making selections from the Current Selections Box
            • Adding a Bookmark Object
            • Aligning and resizing sheet objects
              • Selecting objects
              • Moving objects
              • Resizing objects
                • Resizing a Multi Box
              • Aligning sheet objects
              • Do a little house keeping
            • Creating and applying a default color map
              • Defining chart colors
              • Setting the default color map
            • Summary
            • Chapter 6: Building Dashboards
              • User types
                • Dashboard users
                • Analysts
                • Report users
              • Applying the DAR principle to Airline Operations
                • Document requirements
              • Creating the Analysis sheet
                • Adding a new chart
                • Bar Chart
                  • Additional bar chart properties
                • Expressions and the Edit Expression window
                  • Expressions
                  • The Edit Expression window
                  • The Expression Overview window
                • Line Chart
                  • Additional line chart properties
                • Combo Chart
                • Container
                • Scatter Chart
                • Button
                • Statistics box
              • Creating the new Dashboard sheet
                • Linked Objects
                • Gauges
                  • Cloning the object for re-use
                  • Adding Air Time %
                  • More Gauge styles
                • Adding a Text object
                  • Using a Text Object to display an image
                  • Adding actions to a Text object
                • Adding a Pie chart
                  • Dimension Limits
                  • Adding the dimension value to the data point values
              • Creating the Reports sheet
                • Variables
                  • The Expression Overview window in action
                • Copying sheets
                • KPIs per airline, origin, and destination country
                  • Cyclic and Drill-down groups
                • Straight table
                  • Not all expressions are numbers
                • Pivot tables
                • Auto minimize
                • The Report Editor window
              • Other charts
                • Radar Chart
                • Mekko Chart
                • Grid Chart
                • Funnel Chart
                • Block Chart
                • Trellis Chart
              • Summary
              • Chapter 7: Scripting
                • The Script Editor
                  • Menu and toolbar
                  • Script pane
                  • Tool pane
                • Script statements
                  • Building the aircraft dimension table
                    • Loading the aircraft information
                    • Adding the aircraft groups
                    • Loading the second aircraft table
                    • Making it all right
                  • Manipulating tables
                    • The JOIN statement
                    • The KEEP statement
                    • The CONCATENATE statement
                    • The NOCONCATENATE statement
                    • Using MAPPING tables
                    • Adding comments
                    • Storing tables
                    • Renaming tables and fields
                    • Deleting tables and fields
                  • Setting variables
                  • Controlling script flow
                • Conditional functions
                • Dealing with different data types
                  • Strings
                    • String operators
                    • String functions
                  • Numbers and numeric functions
                  • Date and time functions
                • Debugging script
                  • Syntax check
                  • Saving logs to disk
                  • The script debugger
                    • Using breakpoints
                    • Limited load
                  • Tracing script
                • Standardizing and organizing script
                  • Using tabs
                  • Comments
                  • Adding an information tab
                  • Script layout
                  • Naming conventions
                    • Table naming conventions
                    • Field naming conventions
                • Re-using scripts
                  • Subroutines
                  • Including script files
                • Managing file locations and connection strings
                • Summary
                • Chapter 8: Data Modeling Best Practices
                  • Data consistency
                    • Dealing with dimensions without facts
                      • An alternative approach
                      • A solo exercise
                    • Dealing with facts without dimensions
                  • Reducing storage requirements
                    • Using number-based key fields
                    • Removing unused fields
                    • Splitting high-cardinality fields
                  • Design challenges of data modeling
                    • The Airline Employment statistics table
                    • Concatenating fact tables
                      • Structural asymmetry
                      • Natural and forced concatenation
                      • Concatenating the Employment Statistics table
                    • Working with link tables
                      • A link table example
                      • Creating a link table in the Airline Operations document
                    • Finding a balance
                  • The master calendar
                  • A final note on data modeling
                  • Summary
                  • Chapter 9: Basic Data Transformation
                    • Changing the source table structure
                      • "Cleansing" a dirty table
                        • File contents
                        • Working with the Transformation Step wizard
                        • The final result
                        • Other transformation tricks
                      • Loading a Crosstable
                        • A Crosstable example
                        • Working with the Crosstable Wizard
                        • A solo exercise
                      • Expanding a hierarchy
                        • A hierarchy example
                        • Working with the Hierarchy Wizard
                        • The tree-view list-box
                      • Generic load
                        • Loading a generic table into QlikView
                    • Summary
                    • Chapter 10: Advanced Expressions
                      • Using variables
                        • Creating a variable
                        • Using variables in charts
                        • Interactively changing a variable's value
                          • Using the Input Box object
                          • Using the Slider object
                        • Using variables based on expressions
                        • Using variables to store expressions
                        • Variable naming convention
                        • The Dollar Sign Expansion syntax
                          • Dollar Sign Expansion with parameters
                          • Double Dollar Sign Expansion
                      • Using the TOTAL qualifier
                      • The Aggr function
                        • Using Aggr for nested aggregation
                          • A word on calculated dimensions
                        • Aggregations over the Aggr output
                          • A word on using the Distinct qualifier
                        • Getting the Average Load Factor per Route per Airline
                      • Conditional functions
                        • The If function
                          • The syntax
                          • A use case
                          • Numeric versus text comparisons
                        • The Class function
                          • A solo exercise using the Class function
                        • The Pick function
                          • Using Pick with Dimensionality
                      • A tip on copying expressions
                      • Summary
                      • Chapter 11: Set Analysis and Point In Time Reporting
                        • The magic of Set Analysis
                          • What is it for?
                          • Syntax and examples
                            • Using variables in set expressions
                          • Dynamic record sets
                          • More assignment operators
                          • Set operators
                          • Using element functions
                        • Point In Time Reporting
                          • The challenge
                          • Defining the set modifiers
                            • Obtaining the base period record set
                            • Obtaining the compare-to period record set
                          • Constructing the expressions
                          • Enabling additional period comparisons
                            • More Point In Time Reporting examples
                          • Storing set expressions into variables
                            • Set expressions with parameters
                          • Portable set expressions
                            • Set variables and the Master Calendar
                        • Comparative analysis with alternate states
                          • A comparative analysis example
                          • Alternate states step-by-step
                          • State-based expressions
                            • Combining alternate states and the default state
                          • Applying alternate states to layout objects
                          • Document navigation with alternate states
                            • Clearing selections in an alternate state
                        • Always validate
                        • Summary
                        • Chapter 12: Advanced Data Transformation
                          • Data architecture
                            • Two-stage architecture
                            • Three-stage architecture
                            • Setting up our environment
                          • Loading data already stored in QlikView
                            • Cloning a QlikView data model
                            • Loading from RAM
                              • Resident load
                          • Aggregating data
                            • Aggregating the Flight Data table
                            • The Transformation output
                            • Aggregation functions
                          • Sorting tables
                            • Ordering the Order-By fields
                          • The Peek function
                          • Merging forces
                            • A refresher
                            • The objective
                            • Getting it done
                              • Loading the table
                              • Sorting the table
                              • Peeking previous records
                              • A solo exercise
                          • Dealing with slowly changing dimensions
                            • The Carrier Decode table
                            • IntervalMatch magic
                              • Expanding the intervals
                              • Some considerations
                              • Applying IntervalMatch to the Carrier Decode table
                          • Ordering, peeking, and matching all at once
                            • The use case
                          • Incremental loads
                          • Summary
                          • Chapter 13: More on Visual Design and User Experience
                            • Creating a consistent QlikView UI
                              • Screen resolution
                              • Background image
                              • Themes
                                • Applying themes
                                • Creating themes
                            • Additional interactivity
                              • Triggers
                                • Document triggers
                                • Sheet triggers
                              • Actions
                              • Advanced search expressions
                                • A solo exercise
                              • Dynamic bookmarks
                              • Alerts
                              • Conditionally showing and calculating objects
                            • Summary
                            • Chapter 14: Security
                              • Hidden script
                              • Section access
                                • Section access fields
                                • Reduction fields
                                • Initial data reduction
                                • Omitting fields
                              • Document-level security
                              • Sheet-level security
                              • Summary

                              Miguel García

                              Miguel  is a Business Intelligence consultant and QlikView Solutions Architect from Monterrey, Mexico. Having worked throughout many successful QlikView implementations, from inception through implementation, and performed across a wide variety of roles on each project, his experience and skills range from pre-sales to applications development and design, technical architecture, system administration, as well as functional analysis and overall project execution.

                              He currently holds the QlikView Designer and QlikView Developer Certifications, backed by QlikTech, for versions 9, 10 and 11.

                              His early-found passion for QlikView led him to create and host the iQlik’s blog (http://iqlik.wordpress.com) which has been proved to help many developers, both new comers and experienced, in their everyday development challenges. You can follow his blog updates via twitter on @GarciaMMiguel and @iQlik.

                              He works for DataIQ, a QlikView consulting firm with presence in Argentina, Mexico, Uruguay and Paraguay.

                              Bio in Spanish

                              Miguel Ángel García es un Consultor de Inteligencia de Negocios con base en Monterrey, México. Luego de participar en una variedad de implementaciones exitosas con QlikView, y desempeñarse en una diversidad de roles en cada proyecto, su experiencia incluye el desarrollo y diseño de aplicaciones, pre-venta, arquitectura técnica, administración del sistema, así como también análisis funcional y administración de proyectos.

                              Actualmente cuenta con las certificaciones oficiales otorgadas por QlikTech de QlikView Designer, QlikView Developer y QlikView System Administrator para las versiones 9, 10 y 11 del producto.

                              Su pasión por QlikView lo llevó a crear y administrar el blog de iQlik (http://iqlik.wordpress.com). Puede seguir sus actualizaciones a través de Twitter con @iQlik.

                              Barry Harmsen

                              Barry Harmsen is an independent Business Intelligence consultant based in the Netherlands. Originally from a background of traditional business intelligence, data warehousing and performance management, in 2008 Barry made the shift to QlikView and a more user-centric form of Business Intelligence. Since then he has completed many successful QlikView implementations in various industries, from Financial Services to Telecom, and from Manufacturing to Healthcare. Barry writes a QlikView blog at QlikFix.com

                              Bio in Spanish

                              Barry Harmsen es un Consultor de Inteligencia de Negocios con base en Los Países Bajos. Luego de trabajar con herramientas tradicionales de Inteligencia de Negocios, Almacenamiento de Datos y herramientas de Gestión de Desempeño, en 2008 cambió su enfoque hacia QlikView, por representar una forma de Inteligencia de Negocios centrada en el usuario. Desde entonces, ha participado en muchas implementaciones exitosas con QlikView a través de una variedad de industrias, entre las que se encuentran Servicios Financieros, Telecomunicaciones, Manufactura y Sector Salud. Escribe artículos sobre QlikView en QlikFix.com.

                              Code Downloads

                              Download the code and support files for this book.

                              Submit Errata

                              Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                              - 18 submitted: last submission 11 Jul 2014

                              Errata Type: Typo | Page no 478

                              In step 9, the columns headings in the table need to be switched. So not “%FLIGHTTYPE - Flight Type” but “Flight Type - %FLIGHTTYPE”. Otherwise it does not match with the script that is shown.

                              Errata type: Typo | Page no.479

                              “In this case, that means that ADMIN gets access to the DOMESTIC_FOREIGN, DOMESTIC_US, and INTERNATIONAL_US flight types, but not to the INTERNATIONAL_FOREIGN flight type, since that is not listed in the section access table.”

                              Should be replace by

                              “In this case, that means that ADMIN gets access to the DOMESTIC_FOREIGN, DOMESTIC_US, and INTERNATIONAL_FOREIGN flight types, but not to the INTERNATIONAL_US flight type, since that is not listed in the section access table.”

                              (INTERNATIONAL_FOREIGN and INTERNATIONAL_US needs to be switched)

                              Errata Type: Incorrect image | Page number: 227

                              The image mentioned on this page is not correct. Please refer the following image:

                              Errata Type: Incorrect image | Page number: 224

                              The image mentioned on this page is not correct. Please refer the following image:

                              Errata Type: Code | Page number: 259 

                              The code snippet in the first half of the page give an error

                              There is some problem with the IF statement.

                              <>The IF statement should read:

                              IF '$(SourceTable)' <> '' Then

                              Instead of just:

                              IF '$(SourceTable)' <> ''

                              The soultion file (TraceRowCount.qvs) is correct.

                              Errata type: Code | Page no: 425

                              Drop Table Temp_Employment;

                              Store Employment into [Transformed QVDs\Employment Statistics.QVD] 


                              This should should be changed to the following:

                              Drop Table Temp_Employment;

                              Store Employment into [..\3.QVDs\Transformed\Employment Statistics.QVD] 



                              Errata page: 381
                              There is an Information box for which the last two lines must be changed. The text to be changed currently reads as follows
                              This formula will, for example, assign the value 24120 to December 2009, and 24121 to January 2010, and so on.
                              Instead, it should read:
                              This formula will, for example, assign the value 24120 to December 2010, and 24121 to January 2011, and so on.


                              Errata Type : Code | Page 423 and 425

                              Airline ID mentioned in the code snippets on these pages should be %Airline ID.

                              Errata Type : Technical | Page : 224

                              The last point reads:

                               6. Beneath the LOAD statement for the file Aircraft_2010_Update.csv add the following code: DROP FIELD [%Aircraft Group Type] FROM [Aircraft Types];

                              It should instead be:

                              6. Beneath the LOAD statement for the file Aircraft_Group.csv add the following code: DROP FI ELD [ %Aircraft Group Type] FROM [ Aircraft Types ] ;

                              errata type: Code related Page Number: 430

                              It is: [..\0.Source Layer\XLSX\Intervals.xlsx] (ooxml, embedded labels, table is Elements);

                              It should be: [..\..\1.Source Data\Examples\Intervals.xlsx] (ooxml, embedded labels, table is Elements);

                              Errata type: Technical |  Page Number44

                              It is: 4. Mark the Order by Column checkbox

                              It should be: 4. Uncheck the Order by Column checkbox 

                              Errata type: Code |  Page Number: 268

                              It is: Aircraft Types]:

                              It should be:  [Aircraft Types]:

                              Errata type: Typo|  Page Number: 374

                              It is: On the other hand, of our variable contains text, our expression would be:

                              It should be:  On the other hand, if our variable contains text, our expression would be:

                              Errata type: Technical |  Page Number: 267

                              It is: Now, navigate to the Fields  tab and, from the Available Fields list on the left, highlight the  Year field as well as the  Aircraft Type field, and click on the  Add >  button.

                              It should be:  Now, navigate to the Fields  tab and, from the Available Fields list on the left, highlight the  Year field as well as the  Aircraft Name Type  field, and click on the  Add >  button.

                              Errata Type: Technical  | Page number: 171

                              It is: Add two new segments by clicking twice on the Add... button from the  Segments Setup  section.

                              Please ignore this sentence.

                              Errata Type: Typo | Page number: 189

                              It is: 2.   Click on  Add, enter ePerformedvsScheduled in the Variable Name  input box, and click on  OK. 

                              It should be: 2.   Click on  Add, enter ePerformedVsScheduled in the Variable Name  input box, and click on  OK. 

                              Errata Type: Technical | Page number: 475

                              It is:

                              1.   Save the document by selecting File | Save Entire Document in the menu.

                              2.   Click on  OK to close the  Edit Script  dialog.

                              3.   Close QlikView Desktop by selecting File | Exit  in the menu.

                              4.   Reopen QlikView and the  Airline Operations SA.qvw file.

                              5.   In the  User Identification input box, enter  admin.

                              6.   In the  Password input box, enter  admin123 .

                              It should be: 



                              1. Save the document by selecting File | Save Entire Document in the menu.
                              2. Click on OK to close the Edit Script dialog.
                              3. Reload the document by selecting File | Reload from the menu.
                              4. Save the document once more to preserve the updated/reloaded data.
                              5. Close QlikView Desktop by selecting File | Exit in the menu.
                              6. Reopen QlikView and the Airline Operations SA.qvw file.
                              7. In the User Identification input box, enter admin.
                              8. In the Password input box, enter admin123. 



                              Suggestion: For code testing in Chapter 6, the “show all sheets and objects” option if it has been enabled, it will have to be disabled to make the code work. Please refer to the note on page 468 of the book.

                              Sample chapters

                              You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                              Frequently bought together

                              QlikView 11 for Developers +    JIRA 5.2 Essentials =
                              50% Off
                              the second eBook
                              Price for both: €40.00

                              Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                              What you will learn from this book

                              • Learn the techniques and best practices to transform data in QlikView via script
                              • Loading data from disparate sources to build an associative Data Model
                              • Building robust data models and overcoming common modeling challenges
                              • Designing data visualization objects to present performance measures with charts and tables
                              • Learn to use dimensions and expressions in QlikView objects
                              • Utilise QlikView’s built-in aggregation functions to achieve complex calculations
                              • Creating Point In Time reporting and achieving complex calculations using Set Analysis
                              • Creating a consistent and interactive user interface
                              • Ensuring your QlikView applications and data are protected
                              • Constructing a data architecture that supports scalable QlikView deployments
                              • Learn time-saving techniques for making your QlikView development more efficient

                              In Detail

                              Business Intelligence technologies are a must-have in every business to make informed decisions and keep up-to speed with the ever-evolving markets. QlikView’s disruptive technology makes it a key player and leader in the industry; with its flexibility and powerful analytics environment, building QlikView apps can be mastered by both, business users as well as developers.

                              This book will help you learn QlikView Development from a basic to a practitioner level using a step-by-step approach in a practical environment, and apply proven best practices on each topic.

                              Throughout the book, we will build a QlikView app based on real data about Airline Operations that will help "HighCloud Airlines" make informed business decisions and analysis-guided strategies. HighCloud Airlines executives are evaluating if entering the US market is a good strategy and, if so, which line of business should they focus their investments on; they need QlikView to make the best decision.

                              The application will be evolving chapter by chapter, along with your skills, going from a simple proof of concept to creating a Data Model, adding a custom style, building a Dashboard and handling and manipulating the source data via script. We will meet the "HighCloud Airlines" requirement by using many different data visualization objects and time-saving techniques.

                              The whole application uses real data taken from the Bureau of Transportations statistics of the US and encompasses the operations of Airlines both domestic and international. With three years worth of data, you will help HighCloud Airlines discover where people travel the most, which are the Carriers with the most market share, what is the average load factor per airline, which aircraft is the most used to perform flights, which are the busiest airports, and a whole universe of new insights.


                              It will be a step-by-step tutorial that will discuss best practices. The book is structured in such a way that it can be read both from start to end or can be dipped into.

                              Who this book is for

                              If you are a developer who is looking to learn a fast and easy way to learn to develop your business intelligence apps with QlikView, then this book is for you. If you are a power-user in a QlikView environment, then you will find quicker ways of working with QlikView.

                              You should know the basics of business intelligence before you pick up this book.

                              This book covers QlikView Desktop Personal Edition. Deployments to QlikView Server/Publisher are out of scope for this book.

                              Code Download and Errata
                              Packt Anytime, Anywhere
                              Register Books
                              Print Upgrades
                              eBook Downloads
                              Video Support
                              Contact Us
                              Awards Voting Nominations Previous Winners
                              Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                              Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software