QlikView · Advanced Data Visualization by Pover, Karl -- Read -- Imperial Library of Trantor

Index

QlikView: Advanced Data Visualization

Table of Contents 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

← Prev
Back
Next →

← Prev
Back
Next →