QlikView 11 for Developers

QlikView 11 for Developers

by Miguel García, Barry Harmsen
Released November 2012
Publisher(s): Packt Publishing
ISBN: 9781849686068

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This book is smartly built around a practical case study ? HighCloud Airlines ? to help you gain an in-depth understanding of how to build applications for Business Intelligence using QlikView. A superb hands-on guide.

  • 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.

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.

Table of contents

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

Product information

  • Title: QlikView 11 for Developers
  • Author(s): Miguel García, Barry Harmsen
  • Release date: November 2012
  • Publisher(s): Packt Publishing
  • ISBN: 9781849686068