QlikView: Advanced Data Visualization电子书

QlikView: Advanced Data Visualization

QlikView: Advanced Data Visualization

Why subscribe?

PacktPub.com

Contributors

About the authors

Packt is Searching for Authors Like You

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Conventions used

Get in touch

Reviews

1. Performance Tuning and Scalability

Reviewing basic performance tuning techniques

Removing unneeded data

Reducing the number of rows

Reducing the number of columns

Replacing text keys with numbers

Resolving synthetic keys

Reviewing the basics

Generating test data

Generating dimension values

Generating fact table rows

Understanding how QlikView stores its data

A great primer

Looking at things from a simple level

Exporting the memory statistics for a document

Strategies to reduce the data size and improve performance

Optimizing field values and keys

Optimizing data by removing keys using ApplyMap

Optimizing performance by removing keys by joining tables

Optimizing memory by removing low cardinality fields

Testing chart performance for different load options

Turning the cache off

Examining the chart calculation time for different scenarios

Optimizing performance by creating counter fields

Optimizing performance by combining fact tables?

Optimizing your numbers

Optimizing chart calculation times

The QlikView calculation engine

Creating flags for well-known conditions

Sorting for well-known conditions

Using Direct Discovery

Direct Discovery restrictions

Direct Discovery syntax

Looking at an example Direct Query

Testing scalability with JMeter

Obtaining the scalability tools

Installing JMeter

Installing the scalability tools

About the scalability tools

Running an example execution

Summary

2. QlikView Data Modeling

Reviewing basic data modeling

Associating data

Automatically associating tables

Understanding synthetic keys

Creating composite keys

Using string concatenation

Using one of the Hash functions

Using the AutoNumber function

Realizing that facts are calculated at the level of their table

Joining data

Understanding Join and Keep

Inner joins

Left and right joins

Outer joins

Cartesian joins

Understanding the effect of duplicate key values on joins

Understanding Keep

Concatenating rows

Reviewing Concatenate

Differentiating Concatenate and Join

Mapping data with ApplyMap

Reviewing the basic functionality of ApplyMap

Mapping numbers

Using ApplyMap instead of Join with duplicate rows

Dimensional data modeling

Differentiating between facts and dimensions

Understanding the grain

Understanding star schemas

Summing with facts

Discovering more about facts

Transaction fact tables

Periodic snapshot fact tables

Factless fact tables

Dealing with nulls in fact tables in QlikView

Designing dimension tables

Denormalizing dimensions and conformed dimensions

Understanding surrogate keys

Dealing with missing or late arriving dimension values

Defining Kimball's four-step dimensional design process

Selecting the business process

Declaring the grain

Identifying the dimensions

Identifying the facts

Learning some useful reusable dimension methods

Creating a calendar dimension

Unwrapping hierarchies

Creating leaves with Hierarchy

Creating parent associations with HierarchyBelongsTo

Creating dimensional facts

Handling slowly changing dimensions

Taking the most recently changed record using FirstSortedValue

Using IntervalMatch with SCDs

Using hash to manage from/to dates

Dealing with multiple fact tables in one model

Joining the fact tables together

Concatenating fact tables

Changing the grain of a fact table

Linking fact tables of different grains

Drilling across with document chaining

Summary

3. Best Practices for Loading Data

Reviewing data loading concepts

Getting data from anywhere

Loading data from QlikView

Loading similar files with concatenation

Loading dissimilar files with Concatenate and For Each

Understanding QlikView Data files

Storing tables to QVD

Using QVD files

Understanding why you should use an ETL approach

Speeding up overall data loading

Reusing extracted data in multiple documents

Applying common business rules across multiple documents

Creating conformed dimensions

Provisioning a self-service data layer

Using an ETL approach to create QVD data layers

Creating a StoreAndDrop subroutine

Extracting data

Creating an extractor folder structure

Differentiating types of scripts

Executing the extractors

Transforming data

Creating a transformer and model folder structure

Executing transformers

Loading data

Creating a UserApp folder structure

Executing the load step

Mastering loading techniques

Loading data incrementally

Establishing the script for the basic process

Running an incremental load when data is only added

Loading incrementally when data might be modified

Handling deletions from the source system

Handling situations where there is no modify date

Partially reloading only one part of the data model

Replacing a table

Adding new rows to a table

Managing script execution in partial reloads

Loading the content of another QVW

Using QlikView Expressor for ETL

Introducing Expressor

Understanding why to use Expressor for ETL

Understanding workspaces, libraries, projects, and artifacts

Creating a workspace

Managing extensions

Working with libraries and projects

Understanding artifacts

Configuring connections

Configuring a File connection

Connecting to a database

Creating a QVX Connector Connection

Configuring types and schemas

Adding additional Atomic types

Creating Composite types

Configuring a schema

Creating and packaging a basic dataflow

Understanding the dataflow toolbox

Inputs

Outputs

Transformers

Utility

Creating the dataflow

Configuring a Read File operator

Adding a Transformation operation

Creating a QVX output

Packaging the dataflow

Summary

4. Advanced Expressions

Reviewing basic concepts

Searching in QlikView

Searching for text

Wildcard search

Normal search

Fuzzy search

Associative search

Advanced search

Searching numeric fields

Numeric search

Automatic interpretation of searches

Multiple values search

Searching in multiple listboxes

Understanding bookmarks

Saving a bookmark

Managing bookmarks

Using variables in QlikView

SET versus LET

Using variables to hold common expressions

Using variables with Dollar-sign Expansion

Limiting calculations

Sum of If

Flag arithmetic

Calculations using variables

Data islands

Set Analysis

Explaining what we mean by a set

Set identifiers

Set modifiers

Understanding Dollar-sign Expansion

Following the two-step process

Following the steps in the script debugger

Following the steps in a chart expression

Understanding when the steps happen in chart expressions

Using parameters with variables and Dollar-sign Expansion

Using variables in expressions

Using advanced Set Analysis

Identifying the identifiers

Understanding that modifiers are sets

Set arithmetic

Using searches in Set Analysis

Using Dollar-sign Expansion with Set Analysis

Comparing to other fields

Direct field comparison

Using Concat with Dollar-sign Expansion

Using the P and E element functions

Set Analysis with Alternate States

Using Alternate States as identifiers

Comparing fields between states

Calculating vertically

Using inter-record and range functions

Applying the Total qualifier

Creating advanced aggregations with Aggr

Using Aggr to calculate a control chart

Calculated dimensions

No to nodistinct

Summary

5. Advanced Scripting

Reviewing the basic concepts

Using Table Files Wizard

Using relative paths

Delimited files

Fixed width files

XML files

HTML files

QVD/QVX files

Connecting to databases

Using the Connect button

Understanding the Connect To statement

Explaining the Force 32 Bit option

The Select wizard

Counting records

RecNo

RowNo

FieldValueCount

NoOfRows

NoOfColumns

Loading data quickly

Understanding compression settings

Optimal loading from QVD

Using an Exists clause

Preloading fields into QVDs

Applying variables and the Dollar-sign Expansion in the script

Examining common usage

Holding dates

Holding paths

Examining variable values during reloads

Nesting Dollar-sign Expansions

Passing parameters to variables – macro functions

Subroutines

Using control structures

Branching with conditional statements

If … Then … ElseIf

A note about conditional functions

Switch … Case

When and Unless

Looping in the script

AutoGenerate

For … Next loops

For Each … Next loops

FileList

DirList

Do … Loop

Exiting

Exiting the script

Exiting other constructs

Using variables for error handling

ErrorMode

ScriptError

ScriptErrorCount and ScriptErrorList

Examining advanced Table File Wizard options

Enabling a transformation step

Garbage

Fill

Column

Context

Unwrap

Rotate

Using the Crosstable wizard

Looking at data from different directions

Putting things first

First

FirstSortedValue

Looking backwards

Previous

Peek

Reusing code

Summary

6. What's New in QlikView 12?

Common QIX Engine

64-bit only

Online documentation

Security improvements

Mobile touch improvements

Improved clustering and scaling

Clustering improvements in QlikView 12.1

What is new in the Qlik product portfolio?

Qlik Sense Enterprise and the Qlik Analytics Platform

In what way are QlikView and Qlik Sense similar?

In what way do QlikView and Qlik Sense differ?

What does this mean for users?

What is the Qlik Analytics Platform?

Qlik NPrinting

Qlik Web Connectors

Qlik GeoAnalytics

Qlik DataMarket

How do the products in the Qlik product portfolio fit together?

Summary

7. Styling Up

Design requirements

The Document Properties window

The Sheet Properties dialog

Setting the object properties

Caption colors and style

Changing the caption colors

The Color Area and Color dialog windows

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

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

Style

Presentation

Expressions and the Edit Expression window

Expressions

The Edit Expression window

Fields

Functions

Variables

Images

The Expression Overview window

Line Chart

Additional line chart properties

Expressions

Style

Presentation

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

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

10. Security

Hidden script

Section access

Section access fields

Reduction fields

Initial data reduction

Omitting fields

Document-level security

Sheet-level security

Summary

11. Data Visualization Strategy

Data exploration, visualization, and discovery

Data teams and roles

Data research and development

Data governance team

Agile development

User story

Minimum Viable Product

QlikView Deployment Framework

Exercise 11.1

Summary

12. Sales Perspective

Sales perspective data model

Exercise 12.1

Data quality issues

Missing dimension values

Missing fact values

Data formatting and standardization

Case

Unwanted characters

Dates and time

Master calendar

Customer stratification.

Pareto analysis

Exercise 12.2

Exercise 12.3

Customer churn

Exercise 12.4

Exercise 12.5

QlikView extensions and the cycle plot

Exercise 12.6

Governance – design template

Summary

13. Financial Perspective

Financial perspective data model

Exercise 13.1

Financial report metadata

AsOfCalendar

Income statement

Exercise 13.2

Custom format cell

Exercise 13.3

Balance sheet

Exercise 13.4

Exercise 13.5

Cash flow statement

Exercise 13.6

Summary

14. Marketing Perspective

Marketing data model

Customer profiling

Parallel coordinates

Exercise 14.1

Exercise 14.2

Sankey

Exercise 14.3

Exercise 14.4

Market size analysis

Exercise 14.5

Exercise 14.6

Exercise 14.7

Social media analysis

Exercise 14.8

Exercise 14.9

Exercise 14.10

Sales opportunity analysis

Exercise 14.11

Summary

15. Working Capital Perspective

Working capital data model

Rotation and average days

Days Sales of Inventory

Exercise 15.1

Days Sales Outstanding

Exercise 15.2

Days Payable Outstanding

Exercise 15.3

Exercise 15.4

Working capital breakdown

Exercise 15.5

Inventory stock levels

Exercise 15.6

Aging report

Exercise 15.7

Customer stratification

Stratification by distribution

Exercise 15.8

Exercise 15.9

Visualizing stratification

Exercise 15.10

Summary

16. Operations Perspective

Operations data model

Handling multiple date fields

On-Time and In-Full

Exercise 16.1

OTIF breakdown

Exercise 16.2

Exercise 16.3

Predicting lead time

Exercise 16.4

Exercise 16.5

Supplier and On-Time delivery correlation

Exercise 16.5

Planning in QlikView with KliqPlan

Planning tool extensions

Sales forecasts and purchase planning

Other applications

Summary

17. Human Resources

Human resources data model

Slowing changing dimensions attributes

Personnel productivity

Exercise 17.1

Exercise 17.2

Personnel productivity breakdown

Age distribution

Exercise 17.3

Salary distribution

Exercise 17.4

Employee retention rate

Exercise 17.5

Employee vacation and sick days

Exercise 17.6

Employee training and performance

Exercise 17.7

Personal behavior analysis

Exercise 17.8

Summary

18. Fact Sheets

Customer fact sheet consolidated data model

Customer Fact sheet Agile design

Creating user stories

User story flow

Converting user stories into visualizations

Going beyond the first visualization

Customer Fact sheet advanced components

Bullet graph

Exercise 18.1

Exercise 18.2

Sparklines

Exercise 18.3

Customizing the QlikView User Experience

Quick access to supplementary information

Exercise 18.4

Dynamic data visualization

Exercise 18.5

Regional settings

Currency

Language

Date and number formats

Customer Fact sheet n QlikView

Summary

19. Balanced Scorecard

The Balanced Scorecard method

The financial perspective

The customer perspective

The internal business process perspective

The learning and growth perspective

The Balanced Scorecard consolidated data model

The Balanced Scorecard information dashboard design

The Gestalt principles of perceptual organization

Proximity

Enclosure

Closure

Connection

Continuity

Similarity

Creating the filter pane bubble

Exercise 19.1

Creating an interactive tutorial

Exercise 19.2

Measuring success with XmR charts

Exercise 19.3

Summary

20. Troubleshooting Analysis

Troubleshooting preparation and resources

Positive mindset

General debugging skills

Reproduce

Diagnose

Fix

Reflect

Resources

QlikView Help

Local knowledge base

Qlik Community

Qlik Support

Reporting issues

Common QlikView application issues

Common QlikView data model issues

All expression values are exactly the same

The expression total is not equal to the sum of the rows

Duplicate values in a list box

Data doesn't match user expectation

Common QlikView expression issues

The expression does not calculate every row

The amounts in the table are not accumulating

Summary

21. Mastering Qlik Sense Data Visualization

Qlik Sense and QlikView developers

Visualization extension examples for cross-selling

Plan to master Qlik Sense data visualization

Summary

Index