QlikView Essentials

By Sinha Chandraish

Want to solve your Business Intelligence headaches? Learn how QlikView can help, and discover a powerful yet accessible BI solution that lets you harness your data

About This Book

- Design interactive dashboards using QlikView to share sharp BI insights
- Discover how to create effective data models to manage and harness your data with QlikView’s ETL functionality
- Learn and implement best practices to successfully integrate QlikView within your organization

Who This Book Is For

If you recognize the challenges of harnessing data for a modern business this book is for. Maybe you already know a little about QlikView – if you want to learn more, QlikView Essentials is a great way to develop your knowledge and skills.

What You Will Learn

- Learn the complete QlikView workflow – from loading data, to visualization and analytics
- Learn how to Load data from different sources, including QVD files and how to optimize data models for accuracy and precision
- Discover solutions to common data modeling problems, so you can respond quickly to changing situations
- Create accessible dashboards and quality data visualizations to share insights effectively
- Learn how to deploy your BI application for optimal availability

In Detail

This guide demonstrates just how easy it is to get started with QlikView and create your own BI application. Featuring an introduction to its core features before exploring how to load data and model it, you’ll soon become more confident that you can take full advantage of QlikView’s capabilities. You will also learn how to use QVD files with QlikView – and how they offer a simpler way of handling data.
After digging deeper into data handling, as you learn how to use mapping tables and create a master calendar, you’ll then find out how to get the most from QlikView’s visualization features – vital if you are to use your data insights effectively. From accessible and user friendly dashboards to strategies and best practices for subjecting data to further analysis, you can be confident that you’ll be prepared to get the most out of your data with QlikView.
With details on how to finally secure your application and deploy it for a successful integration in your organization, QlikView Essentials underlines exactly why QlikView is becoming more and more popular for businesses that understand the value of data.

Style and approach

The book takes a step-by-step approach to QlikView development, and is explained in an easy-to-follow style. Each topic is accompanied by an exercise so you can put your knowledge into practice. This book will be useful for you no matter what your needs and experience levels with the technology.

• Language: English
• Publisher: Packt Publishing
• Release date: Jan 22, 2016
• ISBN: 9781784395087

Book preview

Table of Contents

QlikView Essentials

Credits

About the Author

About the Reviewer

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. QlikView Fundamentals

QlikView components

QlikView installation

Getting started

The development life cycle of QlikView implementations

About Adventure Works

User requirements

Analyze data model/data sources

Star schema and snow flake schema

QlikView development setup

Summary

2. Extract, Transform, and Load

Configuring settings

Scripting essentials

Connecting to the database

Loading the OrderHeader Table

Loading the OrderDetail table

Creating fields in the Order table

Loading the Customer table

Loading the Product table

Resolving the synthetic keys

How to resolve synthetic keys

Removing synthetic keys between the Product and Order Detail tables

Loading the ProductSubcategory table

Loading data from text and Excel files

Loading the Product Category table

Loading Employee.xlsx

Circular reference and loosely coupled tables

Circular reference

Loosely coupled tables

Resolving circular reference

Loading the SalesTerritory and Shipment tables

Loading an Inline table

The Qualify statement

Loading the Shippers table

A word about resident load and Exists

Finding the sales person

Using If statements in the script

Including files using the Include statement

More on Table Viewer

Time to review your data model so far

Summary

3. Optimizing Your Data Model

Mapping table essentials

Mapping load – Territory table

Mapping load – Product Category and ProductSubcategory tables

Mapping load – ProductSubcategory table

Mapping load – CountryRegion_Inline

Concatenation

Concatenation example

Automatic Concatenation

NoConcatenate

Forced concatenation

Joins

Join Orders and Order Detail

Left join SalesPerson

Aggregating data

Concatenating the new Employees table

Reviewing the final data model

Summary

4. Data Modeling Challenges

Crosstable essentials

Loading EmployeeSalesTarget.xlsx

Link table

Link table essentials

Creating a link table

Variables in QlikView

Using variables to set the file path

Master calendar

The IntervalMatch function

QlikView Data Files (QVD)

Creating QVD files for our data model

Optimized load – reading QVD

Reading QVD files

Best practices of data modeling

Summary

5. Creating Dashboards

Dashboarding essentials

Getting started

Associative experience

Binary load

Exploring menu items

Sheet objects

Creating a multi-tab application

The dashboard sheet

Creating list boxes for other fields

Options to copy and paste objects

Placing a logo on the dashboard sheet

Creating a bar chart

Expression Overview

Creating a text object

Creating a scatter chart

The story of two tables

Creating a straight table

Enhancing your tables

Groups

Creating a drill-down group

Creating a cyclic group

Creating a pivot table

Container

Dimension limits

Interactivity using text objects

Some more charts

Line charts

Combo charts

Pie charts

Some important functions

The Aggr function

Nested aggregation

Other functions

Summary

6. Comparative Analysis

Set Analysis essentials

Using Set Analysis

Alternate States

What-If Analysis

Using input box

The last step

Summary

7. Securing Your Application

Section Access essentials

Implementing Section Access

Using OMIT

Using reduction

Summary

8. Application Deployment

QlikView architecture essentials

Client Access Licenses (CALs)

QlikView application deployment

QlikView server structure

Summary

Index

QlikView Essentials

---

QlikView Essentials

Copyright © 2016 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: January 2016

Production reference: 1120116

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78439-728-9

www.packtpub.com

Credits

Author

Chandraish Sinha

Reviewer

Julián Villafuerte

Commissioning Editor

Akram Hussain

Acquisition Editors

Purav Motiwalla

Larissa Pinto

Content Development Editor

Samantha Gonsalves

Technical Editor

Pramod Kumavat

Copy Editor

Joanna McMahon

Project Coordinator

Kinjal Bari

Proofreader

Safis Editing

Indexer

Rekha Nair

Graphics

Kirk D'Penha

Production Coordinator

Manu Joseph

Cover Work

Manu Joseph

About the Author

Chandraish Sinha is a Business Intelligence enthusiast from Ohio. He brings 17 years of experience in providing cost-effective BI solutions.

He is responsible for many QlikView implementations in various industries, namely financial, insurance, pharmaceuticals, and event management. He currently holds QlikView Designer and QlikView Developer certifications.

He shares his knowledge and passion through his QlikView blog (http://www.learnallbi.com/).

He currently works as an independent BI consultant and helps organizations in implementing BI solutions.

I would like to thank my family and friends to provide me time and encouragement in the process of writing this book.

About the Reviewer

Julián Villafuerte is a founding member of Evolution Consulting, a Mexican firm, which provides QlikView consulting services throughout America. Since 2010, he has helped several companies to define effective strategies for data management and business analysis. As a consultant, he has worked in application development, project management, presales, and training divisions for many industries, including retail, manufacturing, and insurance.

In October, 2015, he published Creating Stunning Dashboards with QlikView, Packt Publishing, a practical handbook focused on developing useful and engaging analytical applications. He has a Master's degree in Information Technology Management and teaches at the Tecnológico de Monterrey in Mexico City. Recently, he started a blog named QlikFreak (https://qlikfreak.wordpress.com/), where he shares tips and tricks about data visualization, scripting, and best practices.

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

https://www2.packtpub.com/books/subscription/packtlib

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.

Instant updates on new Packt books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter or the Packt Enterprise Facebook page.

Preface

The data is growing at a higher pace so does the need to understand data. There are many applications that perform data analysis and design, but QlikView takes Business Intelligence to the next level. The ability of QlikView to extract and present the data in a way that the human mind thinks, has made QlikView hugely popular. The associative nature of the QlikView data model has made business discovery fairly simple.

This book is being designed in a way that provides equal value to a novice BI developer and a seasoned practitioner. This book starts with the basics of QlikView, data warehousing and works through creating data models and visualizations. This book covers all the topics for the QlikView designer and developer and can be used as a reference guide in new or ongoing implementations.

Each chapter in the book follows a structure:

Each chapter will cover the essentials of the topic in the chapter.

This book covers both QlikView developer/Data model and QlikView designer/visualization topics.

Each topic is explained first and then followed by a step–by-step exercise. Readers can follow these exercises to create their own data model and dash boarding application.

This book uses the Adventure Works database; most readers will have familiarity with this database. There is tons of information available online on this database, so users will easily understand.

This book also comes with data in MS Access, Excel, and text files. It is also accompanied with QlikView solutions/qvw's that the reader can download and follow.

What this book covers

Chapter 1, QlikView Essentials, provides the basics of QlikView. It gives an overview of the QlikView architecture. It also provides instructions on how to download QlikView. You get to know about the star schema and learn about the underlining data model used in the book.

Chapter 2, Extract, Transform, and Load, as the name suggests, dives into building a data model in QlikView by extracting, transforming, and loading data. In this chapter, readers will learn about using scripts to load data from different