QlikView 11 for Developers

Table of Contents

• Preface

• Chapter 1: Meet QlikView
• What is QlikView?
• How does QlikView differ from traditional BI?
• Associative user experience
• Technology
• Adoption path
• Exploring data with QlikView
• Getting QlikView
• Navigating the document
• Slicing and dicing your data
• List-boxes
• Selections in charts
• Search
• Bookmarking selections
• Undoing selections
• Changing the view
• Cyclic Groups
• Drill down Groups
• Containers
• But wait, there's more!
• The technology and components behind QlikView
• The way the data flows
• When QlikView use expands
• Create content
• Reload, publish, and distribute content
• Consume content

• Meet HighCloud Airlines
• Summary

• Chapter 2: Seeing is Believing
• What is a SiB?
• Preparing the workspace
• Setting up the folder structure
• Creating the QlikView document
• Creating the app
• The requirements
• Constructing the data model
• What is a data model?
• Loading the fact table
• Playing with listboxes
• Associating additional tables
• Creating the dashboard tab
• Creating and positioning the filters and user controls
• Number of flights over time
• One chart and multiple analyses with cyclic expressions
• Adding a time drill-down group
• Top 10 routes
• Summary

• Chapter 3: Data Sources
• Using ODBC and OLE DB drivers
• Installing the drivers
• Accessing custom data sources
• Third-party custom connectors
• Reading table files

• Extracting data—two hands-on examples
• Extracting data from MS Access
• Configuring the driver
• Creating the OLE DB connection string
• Querying the database
• Reloading the script
• The resulting data model
• Loading a table file
• Specifying the file attributes
• The transformation step
• Refining the input table
• The resulting script

• QVD and QVX files
• QVD files
• QVX files

• Loading an Inline table
• Summary

• Chapter 4: Data Modeling
• Dimensional data modeling
• Back in the day
• Relational databases and ER modeling
• Dimensional modeling
• The star schema
• The snowflake schema
• Creating the dimensional model
• Dimensional models in QlikView
• The associative data model
• Guidelines for table associations
• How associations are created
• Avoiding data model conflicts
• The Table Viewer window
• Table information
• Field information
• Table preview
• Table viewer menu
• Summary

• Chapter 5: Styling Up
• Design requirements
• The Document Properties window
• The Sheet Properties dialog
• Setting the object properties
• Caption colors and style
• Changing the caption colors
• 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

• Chapter 6: 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
• Expressions and the Edit Expression window
• Expressions
• The Edit Expression window
• The Expression Overview window
• Line Chart
• Additional line chart properties
• 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

• Chapter 7: Scripting
• The Script Editor
• Menu and toolbar
• Script pane
• Tool pane
• Script statements
• Building the aircraft dimension table
• Loading the aircraft information
• Adding the aircraft groups
• Loading the second aircraft table
• Making it all right
• Manipulating tables
• The JOIN statement
• The KEEP statement
• The CONCATENATE statement
• The NOCONCATENATE statement
• Using MAPPING tables
• Adding comments
• Storing tables
• Renaming tables and fields
• Deleting tables and fields
• Setting variables
• Controlling script flow
• Conditional functions
• Dealing with different data types
• Strings
• String operators
• String functions
• Numbers and numeric functions
• Date and time functions
• Debugging script
• Syntax check
• Saving logs to disk
• The script debugger
• Using breakpoints
• Limited load
• Tracing script
• Standardizing and organizing script
• Using tabs
• Comments
• Adding an information tab
• Script layout
• Naming conventions
• Table naming conventions
• Field naming conventions
• Re-using scripts
• Subroutines
• Including script files
• Managing file locations and connection strings
• Summary

• Chapter 8: Data Modeling Best Practices
• Data consistency
• Dealing with dimensions without facts
• An alternative approach
• A solo exercise
• Dealing with facts without dimensions
• Reducing storage requirements
• Using number-based key fields
• Removing unused fields
• Splitting high-cardinality fields
• Design challenges of data modeling
• The Airline Employment statistics table
• Concatenating fact tables
• Structural asymmetry
• Natural and forced concatenation
• Concatenating the Employment Statistics table
• Working with link tables
• A link table example
• Creating a link table in the Airline Operations document
• Finding a balance
• The master calendar
• A final note on data modeling
• Summary

• Chapter 9: Basic Data Transformation
• Changing the source table structure
• "Cleansing" a dirty table
• File contents
• Working with the Transformation Step wizard
• The final result
• Other transformation tricks
• Loading a Crosstable
• A Crosstable example
• Working with the Crosstable Wizard
• A solo exercise
• Expanding a hierarchy
• A hierarchy example
• Working with the Hierarchy Wizard
• The tree-view list-box
• Generic load
• Loading a generic table into QlikView
• Summary

• Chapter 10: Advanced Expressions
• Using variables
• Creating a variable
• Using variables in charts
• Interactively changing a variable's value
• Using the Input Box object
• Using the Slider object
• Using variables based on expressions
• Using variables to store expressions
• Variable naming convention
• The Dollar Sign Expansion syntax
• Dollar Sign Expansion with parameters
• Double Dollar Sign Expansion
• Using the TOTAL qualifier
• The Aggr function
• Using Aggr for nested aggregation
• A word on calculated dimensions
• Aggregations over the Aggr output
• A word on using the Distinct qualifier
• Getting the Average Load Factor per Route per Airline
• Conditional functions
• The If function
• The syntax
• A use case
• Numeric versus text comparisons
• The Class function
• A solo exercise using the Class function
• The Pick function
• Using Pick with Dimensionality
• A tip on copying expressions
• Summary

• Chapter 11: Set Analysis and Point In Time Reporting
• The magic of Set Analysis
• What is it for?
• Syntax and examples
• Using variables in set expressions
• Dynamic record sets
• More assignment operators
• Set operators
• Using element functions
• Point In Time Reporting
• The challenge
• Defining the set modifiers
• Obtaining the base period record set
• Obtaining the compare-to period record set
• Constructing the expressions
• Enabling additional period comparisons
• More Point In Time Reporting examples
• Storing set expressions into variables
• Set expressions with parameters
• Portable set expressions
• Set variables and the Master Calendar
• Comparative analysis with alternate states
• A comparative analysis example
• Alternate states step-by-step
• State-based expressions
• Combining alternate states and the default state
• Applying alternate states to layout objects
• Document navigation with alternate states
• Clearing selections in an alternate state
• Always validate
• Summary

• Chapter 12: 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

• Chapter 13: More on Visual Design and User Experience
• Creating a consistent QlikView UI
• Screen resolution
• Background image
• Themes
• Applying themes
• Creating themes
• Additional interactivity
• Triggers
• Document triggers
• Sheet triggers
• Actions
• Advanced search expressions
• A solo exercise
• Dynamic bookmarks
• Alerts
• Conditionally showing and calculating objects
• Summary

• Chapter 14: Security
• Hidden script
• Section access
• Section access fields
• Reduction fields
• Initial data reduction
• Omitting fields
• Document-level security
• Sheet-level security
• Summary

• Index