QlikView Developer Cheat Sheet

This QlikView cheat sheet provides a quick reference guide for QlikView developers. It covers key concepts, commands, and best practices for working with QlikView, making it easier to extract, transform, load, and visualize data efficiently.

1. Basic Concepts

• QlikView Document (QVW): The main file that stores data, scripts, and visualizations.
• Sheet: A tab within a QlikView document where different charts, tables, and objects are displayed.
• List Box: Displays unique values of a field and allows users to filter data.
• Chart: A graphical representation of data in various formats (bar charts, line charts, pie charts, etc.).
• Table Box: Displays raw data from the data model in tabular form.
• Associative Data Model: QlikView automatically associates data tables by common field names.
• Synthetic Key: Automatically generated keys by QlikView when multiple tables share more than one common field. Avoid if possible for better performance.
• Section Access: QlikView’s security feature that controls access to data within a QlikView document.

2. Scripting Commands

Load Script Syntax

The load script is used to load data into QlikView from various sources. Common commands include:

• LOAD: Used to load data into QlikView.

LOAD Field1, Field2, Field3 FROM data_source.csv;

• SQL SELECT: Used to query data from databases.

SQL SELECT Field1, Field2 FROM table_name;

• RESIDENT: Loads data from an existing QlikView table.

LOAD * RESIDENT TableName;

• JOIN: Joins two tables based on a common field.

JOIN LOAD Field1, Field2 FROM data_source;

• LEFT JOIN/RIGHT JOIN/INNER JOIN: Specifies the type of join.

LEFT JOIN LOAD Field1, Field2 FROM another_data_source;

Data Transformation

• RENAME FIELD: Renames a field.

RENAME FIELD OldFieldName TO NewFieldName;

• REPLACE LOAD: Replaces data in an existing table.

REPLACE LOAD Field1, Field2 FROM updated_data.csv;

• WHERE: Filters data during load.

LOAD * FROM data_source WHERE Field1 = ‘Condition’;

• DISTINCT: Loads unique values only.

LOAD DISTINCT Field1 FROM data_source;

3. Set Analysis

Set analysis allows you to create comparative analysis by defining data sets that differ from the current selection. It is widely used to create dynamic calculations in charts.

Basic Syntax

SUM({} Field)

Common Set Modifiers

• ALL: Ignores the current selection.

SUM({1} Sales)

• Current Selection: Limits to current selections.

SUM({$} Sales)

Exclusion: Excludes specific values from the calculation.

SUM({} Sales)

Inclusion: Limits the calculation to specified values.

SUM({} Sales)

Examples

• Total Sales for 2023:

SUM({} Sales)

Sales excluding Region ‘Europe’:

SUM({} Sales)

4. Data Modeling

• Star Schema: A central fact table connected to multiple dimension tables, ensuring fast performance.
  • Example Fact Table: Sales (includes measures like revenue, quantity)
  • Example Dimension Tables: Product, Customer, Date
• Concatenation: Used to merge tables with the same structure.

CONCATENATE (TableName) LOAD Field1, Field2 FROM data_source.csv;

Link Tables: A technique used to resolve synthetic keys by creating a single table that links multiple fact tables through common keys.

5. Chart Expressions

QlikView allows developers to write expressions for custom calculations in charts and tables.

• SUM(): Adds up values.

SUM(Sales)

• AVG(): Calculates the average of a numeric field.

AVG(Revenue)

COUNT(): Counts the number of records or distinct values.

COUNT(CustomerID)
COUNT(DISTINCT OrderID)

• IF(): Conditional logic.

IF(Sales > 1000, ‘High’, ‘Low’)

• RANGEAVG(): Calculates the average of a range of values.

RANGEAVG(SUM(Sales), SUM(Target))

• LEN(): Returns the length of a text field.

LEN(ProductName)

Sorting Data

• Sort by Expression:

Rank(SUM(Sales))

6. QVD Files

• QVD (QlikView Data) files: QVD is a file format used by QlikView for storing data in a compressed format for efficient reloading.
  • Store QVD: Save data to a QVD file.

STORE TableName INTO FileName.qvd;

• Load from QVD: Load data from a QVD file.

7. Performance Optimization

Data Optimization

• Reduce the number of fields: Only load the fields needed in the dashboard.

LOAD Field1, Field2 FROM data_source;

• Use QVDs for faster load times: Store frequently used data in QVD files to reduce reload times.

Application Optimization

• Minimize Calculated Dimensions: Use pre-calculated fields in the script instead of calculating them in the front-end.
• Limit the number of records: For large datasets, use filters to limit data during development.

8. Best Practices

• Avoid Synthetic Keys: Resolve synthetic keys by renaming fields or using link tables.
• Use Descriptive Field Names: Name fields clearly to make dashboards easier to maintain.
• Optimize Load Scripts: Break down complex load scripts into smaller, reusable sections.
• Use Aggregation Functions: Aggregate data at the script level to improve dashboard performance.

9. Error Handling

• Check Errors in Script Execution: Use the TRACE command to identify where errors occur in your load script.

TRACE Loading Customer Data;

ErrorMode: Control how QlikView responds to script errors.

SET ErrorMode = 0; // Ignore errors

10. Security

Section Access Example

Section access controls which users have access to specific parts of the QlikView application.

SECTION ACCESS;
LOAD ACCESS, USERID, REDUCTION
FROM AccessControl.csv;

SECTION APPLICATION;
LOAD * FROM data_source.csv WHERE REDUCTION = REDUCTION;

Row-Level Security

Control data visibility based on the user’s role or identity.

11. Version Control

• Use Git: Implement version control for your QlikView scripts and projects to track changes and collaborate with other developers.

12. Dashboard Design Tips

• Use Consistent Colors: Keep a consistent color scheme across your dashboard for better readability.
• Minimize Clutter: Avoid adding too many charts or objects that can overwhelm the user.
• Use Tooltips: Add tooltips to charts for additional context or explanations.
• Responsive Layouts: Design dashboards that work well on various screen sizes and resolutions.

This cheat sheet is designed to help QlikView developers quickly reference essential concepts and commands. Keep it handy for day-to-day development and troubleshooting as you build efficient, scalable QlikView applications.