Cover image for QlikView: Advanced Data Visualization

QlikView: Advanced Data Visualization

Author Karl Pover , Stephen Redmond , Barry Harmsen , Miguel Angel Garcia
Release Date: 2018/12/01
ISBN: 9781789955996
Topic:
0%
145
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Build powerful data analytics applications with this business intelligence tool and overcome all your business challenges

Key Features

  • Master time-saving techniques and make your QlikView development more efficient
  • Perform geographical analysis and sentiment analysis in your QlikView applications
  • Explore advanced QlikView techniques, tips, and tricks to deliver complex business requirements

Book Description

QlikView is one of the most flexible and powerful business intelligence platforms around, and if you want to transform data into insights, it is one of the best options you have at hand. Use this Learning Path, to explore the many features of QlikView to realize the potential of your data and present it as impactful and engaging visualizations.

Each chapter in this Learning Path starts with an understanding of a business requirement and its associated data model and then helps you create insightful analysis and data visualizations around it. You will look at problems that you might encounter while visualizing complex data insights using QlikView, and learn how to troubleshoot these and other not-so-common errors. This Learning Path contains real-world examples from a variety of business domains, such as sales, finance, marketing, and human resources.

With all the knowledge that you gain from this Learning Path, you will have all the experience you need to implement your next QlikView project like a pro.

This Learning Path includes content from the following Packt products:

  • QlikView for Developers by Miguel Angel Garcia, Barry Harmsen
  • Mastering QlikView by Stephen Redmond
  • Mastering QlikView Data Visualization by Karl Pover

What you will learn

  • Deliver common business requirements using advanced techniques
  • Load data from disparate sources to build associative data models
  • Understand when to apply more advanced data visualization
  • Utilize the built-in aggregation functions for complex calculations
  • Build a data architecture that supports scalable QlikView deployments
  • Troubleshoot common data visualization errors in QlikView
  • Protect your QlikView applications and data

Who this book is for

This Learning Path is designed for developers who want to go beyond their technical knowledge of QlikView and understand how to create analysis and data visualizations that solve real business needs. To grasp the concepts explained in this Learning Path, you should have a basic understanding of the common QlikView functions and some hands-on experience with the tool.

Table of Contents

  1. QlikView: Advanced Data Visualization
    1. Table of Contents
    2. QlikView: Advanced Data Visualization
      1. Why subscribe?
      2. PacktPub.com
    3. Contributors
      1. About the authors
      2. Packt is Searching for Authors Like You
    4. Preface
      1. Who this book is for
      2. What this book covers
      3. To get the most out of this book
        1. Download the example code files
        2. Conventions used
      4. Get in touch
        1. Reviews
    5. 1. Performance Tuning and Scalability
      1. Reviewing basic performance tuning techniques
        1. Removing unneeded data
          1. Reducing the number of rows
          2. Reducing the number of columns
        2. Replacing text keys with numbers
        3. Resolving synthetic keys
        4. Reviewing the basics
      2. Generating test data
        1. Generating dimension values
        2. Generating fact table rows
      3. Understanding how QlikView stores its data
        1. A great primer
        2. Looking at things from a simple level
        3. Exporting the memory statistics for a document
      4. Strategies to reduce the data size and improve performance
        1. Optimizing field values and keys
        2. Optimizing data by removing keys using ApplyMap
        3. Optimizing performance by removing keys by joining tables
        4. Optimizing memory by removing low cardinality fields
      5. Testing chart performance for different load options
        1. Turning the cache off
        2. Examining the chart calculation time for different scenarios
        3. Optimizing performance by creating counter fields
        4. Optimizing performance by combining fact tables?
        5. Optimizing your numbers
      6. Optimizing chart calculation times
      7. The QlikView calculation engine
        1. Creating flags for well-known conditions
          1. Sorting for well-known conditions
      8. Using Direct Discovery
        1. Direct Discovery restrictions
        2. Direct Discovery syntax
        3. Looking at an example Direct Query
      9. Testing scalability with JMeter
        1. Obtaining the scalability tools
        2. Installing JMeter
        3. Installing the scalability tools
        4. About the scalability tools
      10. Running an example execution
      11. Summary
    6. 2. QlikView Data Modeling
      1. Reviewing basic data modeling
        1. Associating data
        2. Automatically associating tables
          1. Understanding synthetic keys
          2. Creating composite keys
            1. Using string concatenation
            2. Using one of the Hash functions
            3. Using the AutoNumber function
        3. Realizing that facts are calculated at the level of their table
        4. Joining data
          1. Understanding Join and Keep
            1. Inner joins
            2. Left and right joins
            3. Outer joins
            4. Cartesian joins
            5. Understanding the effect of duplicate key values on joins
            6. Understanding Keep
          2. Concatenating rows
          3. Reviewing Concatenate
            1. Differentiating Concatenate and Join
          4. Mapping data with ApplyMap
          5. Reviewing the basic functionality of ApplyMap
          6. Mapping numbers
            1. Using ApplyMap instead of Join with duplicate rows
      2. Dimensional data modeling
        1. Differentiating between facts and dimensions
        2. Understanding the grain
        3. Understanding star schemas
        4. Summing with facts
        5. Discovering more about facts
          1. Transaction fact tables
          2. Periodic snapshot fact tables
          3. Factless fact tables
        6. Dealing with nulls in fact tables in QlikView
        7. Designing dimension tables
          1. Denormalizing dimensions and conformed dimensions
          2. Understanding surrogate keys
          3. Dealing with missing or late arriving dimension values
        8. Defining Kimball's four-step dimensional design process
          1. Selecting the business process
          2. Declaring the grain
          3. Identifying the dimensions
          4. Identifying the facts
        9. Learning some useful reusable dimension methods
          1. Creating a calendar dimension
          2. Unwrapping hierarchies
            1. Creating leaves with Hierarchy
          3. Creating parent associations with HierarchyBelongsTo
        10. Creating dimensional facts
      3. Handling slowly changing dimensions
        1. Taking the most recently changed record using FirstSortedValue
        2. Using IntervalMatch with SCDs
        3. Using hash to manage from/to dates
      4. Dealing with multiple fact tables in one model
        1. Joining the fact tables together
          1. Concatenating fact tables
        2. Changing the grain of a fact table
          1. Linking fact tables of different grains
      5. Drilling across with document chaining
      6. Summary
    7. 3. Best Practices for Loading Data
      1. Reviewing data loading concepts
        1. Getting data from anywhere
        2. Loading data from QlikView
        3. Loading similar files with concatenation
        4. Loading dissimilar files with Concatenate and For Each
        5. Understanding QlikView Data files
        6. Storing tables to QVD
        7. Using QVD files
      2. Understanding why you should use an ETL approach
        1. Speeding up overall data loading
        2. Reusing extracted data in multiple documents
        3. Applying common business rules across multiple documents
        4. Creating conformed dimensions
        5. Provisioning a self-service data layer
      3. Using an ETL approach to create QVD data layers
        1. Creating a StoreAndDrop subroutine
        2. Extracting data
          1. Creating an extractor folder structure
          2. Differentiating types of scripts
          3. Executing the extractors
        3. Transforming data
          1. Creating a transformer and model folder structure
          2. Executing transformers
        4. Loading data
          1. Creating a UserApp folder structure
          2. Executing the load step
      4. Mastering loading techniques
        1. Loading data incrementally
          1. Establishing the script for the basic process
          2. Running an incremental load when data is only added
          3. Loading incrementally when data might be modified
          4. Handling deletions from the source system
          5. Handling situations where there is no modify date
        2. Partially reloading only one part of the data model
          1. Replacing a table
          2. Adding new rows to a table
          3. Managing script execution in partial reloads
        3. Loading the content of another QVW
      5. Using QlikView Expressor for ETL
        1. Introducing Expressor
        2. Understanding why to use Expressor for ETL
        3. Understanding workspaces, libraries, projects, and artifacts
          1. Creating a workspace
          2. Managing extensions
          3. Working with libraries and projects
          4. Understanding artifacts
        4. Configuring connections
          1. Configuring a File connection
          2. Connecting to a database
          3. Creating a QVX Connector Connection
        5. Configuring types and schemas
          1. Adding additional Atomic types
          2. Creating Composite types
          3. Configuring a schema
        6. Creating and packaging a basic dataflow
          1. Understanding the dataflow toolbox
            1. Inputs
            2. Outputs
            3. Transformers
            4. Utility
          2. Creating the dataflow
            1. Configuring a Read File operator
            2. Adding a Transformation operation
            3. Creating a QVX output
          3. Packaging the dataflow
      6. Summary
    8. 4. Advanced Expressions
      1. Reviewing basic concepts
        1. Searching in QlikView
          1. Searching for text
          2. Wildcard search
            1. Normal search
            2. Fuzzy search
            3. Associative search
            4. Advanced search
          3. Searching numeric fields
            1. Numeric search
          4. Automatic interpretation of searches
          5. Multiple values search
          6. Searching in multiple listboxes
        2. Understanding bookmarks
          1. Saving a bookmark
          2. Managing bookmarks
        3. Using variables in QlikView
          1. SET versus LET
          2. Using variables to hold common expressions
          3. Using variables with Dollar-sign Expansion
        4. Limiting calculations
          1. Sum of If
          2. Flag arithmetic
          3. Calculations using variables
          4. Data islands
          5. Set Analysis
          6. Explaining what we mean by a set
            1. Set identifiers
            2. Set modifiers
      2. Understanding Dollar-sign Expansion
        1. Following the two-step process
          1. Following the steps in the script debugger
          2. Following the steps in a chart expression
          3. Understanding when the steps happen in chart expressions
        2. Using parameters with variables and Dollar-sign Expansion
        3. Using variables in expressions
      3. Using advanced Set Analysis
        1. Identifying the identifiers
        2. Understanding that modifiers are sets
        3. Set arithmetic
        4. Using searches in Set Analysis
        5. Using Dollar-sign Expansion with Set Analysis
        6. Comparing to other fields
          1. Direct field comparison
          2. Using Concat with Dollar-sign Expansion
          3. Using the P and E element functions
        7. Set Analysis with Alternate States
          1. Using Alternate States as identifiers
          2. Comparing fields between states
      4. Calculating vertically
        1. Using inter-record and range functions
        2. Applying the Total qualifier
        3. Creating advanced aggregations with Aggr
          1. Using Aggr to calculate a control chart
          2. Calculated dimensions
          3. No to nodistinct
      5. Summary
    9. 5. Advanced Scripting
      1. Reviewing the basic concepts
        1. Using Table Files Wizard
          1. Using relative paths
          2. Delimited files
          3. Fixed width files
          4. XML files
          5. HTML files
          6. QVD/QVX files
        2. Connecting to databases
          1. Using the Connect button
          2. Understanding the Connect To statement
          3. Explaining the Force 32 Bit option
          4. The Select wizard
      2. Counting records
        1. RecNo
        2. RowNo
        3. FieldValueCount
        4. NoOfRows
        5. NoOfColumns
      3. Loading data quickly
        1. Understanding compression settings
        2. Optimal loading from QVD
          1. Using an Exists clause
          2. Preloading fields into QVDs
      4. Applying variables and the Dollar-sign Expansion in the script
        1. Examining common usage
          1. Holding dates
          2. Holding paths
        2. Examining variable values during reloads
        3. Nesting Dollar-sign Expansions
        4. Passing parameters to variables – macro functions
        5. Subroutines
      5. Using control structures
        1. Branching with conditional statements
          1. If … Then … ElseIf
          2. A note about conditional functions
          3. Switch … Case
          4. When and Unless
        2. Looping in the script
          1. AutoGenerate
          2. For … Next loops
          3. For Each … Next loops
            1. FileList
            2. DirList
          4. Do … Loop
        3. Exiting
          1. Exiting the script
          2. Exiting other constructs
        4. Using variables for error handling
          1. ErrorMode
        5. ScriptError
          1. ScriptErrorCount and ScriptErrorList
      6. Examining advanced Table File Wizard options
        1. Enabling a transformation step
          1. Garbage
          2. Fill
          3. Column
          4. Context
          5. Unwrap
          6. Rotate
        2. Using the Crosstable wizard
      7. Looking at data from different directions
        1. Putting things first
          1. First
          2. FirstSortedValue
        2. Looking backwards
          1. Previous
          2. Peek
      8. Reusing code
      9. Summary
    10. 6. What's New in QlikView 12?
      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
      7. 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
      8. How do the products in the Qlik product portfolio fit together?
      9. Summary
    11. 7. 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
    12. 8. 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
    13. 9. 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
    14. 10. 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
    15. 11. Data Visualization Strategy
      1. Data exploration, visualization, and discovery
      2. Data teams and roles
        1. Data research and development
        2. Data governance team
      3. Agile development
        1. User story
        2. Minimum Viable Product
      4. QlikView Deployment Framework
        1. Exercise 11.1
      5. Summary
    16. 12. Sales Perspective
      1. Sales perspective data model
        1. Exercise 12.1
        2. Data quality issues
          1. Missing dimension values
          2. Missing fact values
        3. Data formatting and standardization
        4. Case
        5. Unwanted characters
        6. Dates and time
        7. Master calendar
      2. Customer stratification.
        1. Pareto analysis
        2. Exercise 12.2
        3. Exercise 12.3
      3. Customer churn
        1. Exercise 12.4
        2. Exercise 12.5
      4. QlikView extensions and the cycle plot
        1. Exercise 12.6
      5. Governance – design template
      6. Summary
    17. 13. Financial Perspective
      1. Financial perspective data model
        1. Exercise 13.1
        2. Financial report metadata
      2. AsOfCalendar
      3. Income statement
        1. Exercise 13.2
        2. Custom format cell
        3. Exercise 13.3
      4. Balance sheet
        1. Exercise 13.4
        2. Exercise 13.5
      5. Cash flow statement
        1. Exercise 13.6
      6. Summary
    18. 14. Marketing Perspective
      1. Marketing data model
      2. Customer profiling
        1. Parallel coordinates
          1. Exercise 14.1
          2. Exercise 14.2
        2. Sankey
          1. Exercise 14.3
          2. Exercise 14.4
        3. Market size analysis
          1. Exercise 14.5
          2. Exercise 14.6
          3. Exercise 14.7
        4. Social media analysis
          1. Exercise 14.8
          2. Exercise 14.9
          3. Exercise 14.10
        5. Sales opportunity analysis
          1. Exercise 14.11
      3. Summary
    19. 15. Working Capital Perspective
      1. Working capital data model
      2. Rotation and average days
        1. Days Sales of Inventory
          1. Exercise 15.1
        2. Days Sales Outstanding
          1. Exercise 15.2
        3. Days Payable Outstanding
          1. Exercise 15.3
          2. Exercise 15.4
      3. Working capital breakdown
        1. Exercise 15.5
      4. Inventory stock levels
        1. Exercise 15.6
        2. Aging report
          1. Exercise 15.7
      5. Customer stratification
        1. Stratification by distribution
          1. Exercise 15.8
          2. Exercise 15.9
        2. Visualizing stratification
          1. Exercise 15.10
      6. Summary
    20. 16. Operations Perspective
      1. Operations data model
        1. Handling multiple date fields
      2. On-Time and In-Full
        1. Exercise 16.1
        2. OTIF breakdown
        3. Exercise 16.2
        4. Exercise 16.3
        5. Predicting lead time
          1. Exercise 16.4
          2. Exercise 16.5
        6. Supplier and On-Time delivery correlation
          1. Exercise 16.5
      3. Planning in QlikView with KliqPlan
        1. Planning tool extensions
          1. Sales forecasts and purchase planning
          2. Other applications
      4. Summary
    21. 17. Human Resources
      1. Human resources data model
        1. Slowing changing dimensions attributes
      2. Personnel productivity
        1. Exercise 17.1
        2. Exercise 17.2
      3. Personnel productivity breakdown
        1. Age distribution
          1. Exercise 17.3
        2. Salary distribution
          1. Exercise 17.4
        3. Employee retention rate
          1. Exercise 17.5
        4. Employee vacation and sick days
          1. Exercise 17.6
        5. Employee training and performance
          1. Exercise 17.7
      4. Personal behavior analysis
        1. Exercise 17.8
      5. Summary
    22. 18. Fact Sheets
      1. Customer fact sheet consolidated data model
      2. Customer Fact sheet Agile design
        1. Creating user stories
        2. User story flow
        3. Converting user stories into visualizations
        4. Going beyond the first visualization
      3. Customer Fact sheet advanced components
        1. Bullet graph
          1. Exercise 18.1
          2. Exercise 18.2
        2. Sparklines
          1. Exercise 18.3
      4. Customizing the QlikView User Experience
        1. Quick access to supplementary information
          1. Exercise 18.4
        2. Dynamic data visualization
          1. Exercise 18.5
        3. Regional settings
          1. Currency
          2. Language
          3. Date and number formats
        4. Customer Fact sheet n QlikView
      5. Summary
    23. 19. Balanced Scorecard
      1. The Balanced Scorecard method
        1. The financial perspective
        2. The customer perspective
        3. The internal business process perspective
        4. The learning and growth perspective
      2. The Balanced Scorecard consolidated data model
      3. The Balanced Scorecard information dashboard design
        1. The Gestalt principles of perceptual organization
          1. Proximity
          2. Enclosure
          3. Closure
          4. Connection
          5. Continuity
          6. Similarity
      4. Creating the filter pane bubble
        1. Exercise 19.1
        2. Creating an interactive tutorial
        3. Exercise 19.2
      5. Measuring success with XmR charts
        1. Exercise 19.3
      6. Summary
    24. 20. Troubleshooting Analysis
      1. Troubleshooting preparation and resources
        1. Positive mindset
        2. General debugging skills
          1. Reproduce
          2. Diagnose
          3. Fix
          4. Reflect
        3. Resources
          1. QlikView Help
          2. Local knowledge base
          3. Qlik Community
          4. Qlik Support
      2. Reporting issues
      3. Common QlikView application issues
        1. Common QlikView data model issues
          1. All expression values are exactly the same
          2. The expression total is not equal to the sum of the rows
          3. Duplicate values in a list box
          4. Data doesn't match user expectation
        2. Common QlikView expression issues
          1. The expression does not calculate every row
          2. The amounts in the table are not accumulating
      4. Summary
    25. 21. Mastering Qlik Sense Data Visualization
      1. Qlik Sense and QlikView developers
      2. Visualization extension examples for cross-selling
      3. Plan to master Qlik Sense data visualization
      4. Summary
    26. Index
3.145.67.172