QlikView for Developers

QlikView for Developers

by Miguel Ángel García, Barry Harmsen
Released June 2017
Publisher(s): Packt Publishing
ISBN: 9781786469847

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

Drive value and insight by developing business critical applications with QlikView 12

About This Book

  • Develop your own scalable and maintainable QlikView applications
  • Learn time-saving techniques for making your QlikView development more efficient
  • A one-stop guide to developing BI applications with QlikView

Who This Book Is For

This book is for anyone interested in working with QlikView or who has attended QlikView Developer training. The book caters for all QlikView developers, beginners and experts alike, and anyone who wants to improve their QlikView skills.

What You Will Learn

  • Understand important changes made in QlikView 12
  • Learn the techniques and best practices to transform data in QlikView via scripts
  • Load data from disparate sources to build an associative Data Model
  • Build robust data models and overcome common modeling challenges
  • Designing data visualization objects to present performance measures with charts and tables
  • Learn to use dimensions and expressions in QlikView objects
  • Utilize QlikView's built-in aggregation functions to achieve complex calculations
  • Create Point In Time reporting and achieving complex calculations using Set Analysis
  • Create a consistent and interactive user interface
  • Ensure your QlikView applications and data are protected
  • Optimize the QlikView Data Model
  • Constructing a data architecture that supports scalable QlikView deployments
  • Learn time-saving techniques for making your QlikView development more efficient

In Detail

QlikView is one of the most flexible and powerful Business Intelligence platforms around. If you want to build data into your organization, build it around QlikView. Don't get caught in the gap between data and knowledge ? find out how QlikView can help you unlock insights and data potential with ease.

Whether you're new to QlikView or want to get up to speed with the features and functionality of QlikView, this book starts at a basic level and delves more deeply to demonstrate how to make QlikView work for you, and make it meet the needs of your organization. Using a real-world use-case to highlight the extensive impact of effective business analytics, this book might well be your silver bullet for success.

A superb hands-on guide to get you started by exploring the fundamentals of QlikView before learning how to successfully implement it, technically and strategically. You'll learn valuable tips, tricks, and insightful information on loading different types of data into QlikView, and how to model it effectively.

You will also learn how to write useful scripts for QlikView to handle potentially complex data transformations in a way that is simple and elegant. From ensuring consistency and clarity in your data models, to techniques for managing expressions using variables, this book makes sure that your QlikView projects are organized in a way that's most productive for you and key stakeholders.

Style and approach

This book will help you learn QlikView Development from a basic to a practitioner level using a step-by-step approach. It 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.

Table of contents

  1. QlikView for Developers
    1. Table of Contents
    2. QlikView for Developers
    3. Credits
    4. About the Authors
    5. Acknowledgements
    6. About the Reviewer
    7. www.PacktPub.com
    8. Customer Feedback
    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. Downloading the color images of this book
        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. Listboxes
          2. Selections in charts
          3. Search
        4. Bookmarking selections
          1. Creating a new bookmark
          2. Retrieving a bookmark
          3. Undoing selections
        5. Changing the view
          1. Cyclic groups
          2. Drill down groups
          3. Containers
        6. 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. What's New in QlikView 12?
      1. How is QlikView 12 different from QlikView 11?
        1. Common QIX Engine
        2. 64-bit only
        3. Online documentation
        4. Security improvements
        5. Mobile touch improvements
        6. Improved clustering and scaling
          1. Clustering improvements in QlikView 12.1
      2. What is new in the Qlik product portfolio?
        1. Qlik Sense Enterprise and the Qlik Analytics Platform
          1. In what way are QlikView and Qlik Sense similar?
          2. In what way do QlikView and Qlik Sense differ?
          3. What does this mean for users?
          4. What is the Qlik Analytics Platform?
        2. Qlik NPrinting
        3. Qlik Web Connectors
        4. Qlik GeoAnalytics
        5. Qlik DataMarket
      3. How do the products in the Qlik product portfolio fit together?
      4. What is new in QlikView 12 for Developers?
      5. Summary
    12. 3. 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
    13. 4. Data Sources
      1. Using ODBC and OLE DB drivers
        1. Installing the drivers
        2. Accessing custom data sources
        3. Accessing web resources via APIs
          1. Qlik Web Connectors
          2. Qlik REST Connector
        4. Reading table files
      2. 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
        2. Querying the database
          1. The Create Select Statement wizard
            1. Adding the airport tables
        3. Reloading the script
        4. 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
          1. Refining the input table
            1. The Where clause wizard
            2. The crosstable
        3. The resulting script
      4. QVD and QVX files
        1. QVD files
        2. QVX files
      5. Loading an Inline table
      6. Summary
    14. 5. 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
    15. 6. 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
    16. 7. 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
    17. 8. 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
    18. 9. 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
    19. 10. 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
    20. 11. 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
    21. 12. 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
    22. 13. 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
    23. 14. 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
    24. 15. 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
    25. Index

Product information

  • Title: QlikView for Developers
  • Author(s): Miguel Ángel García, Barry Harmsen
  • Release date: June 2017
  • Publisher(s): Packt Publishing
  • ISBN: 9781786469847