Search Recipes

Blog Example (38)

AltState Variables

Share

Loading Varying Column Names

  Loading Varying Column Names - Qlik Sense
Version 1, Dec 10, 2018

  Loading Varying Column Names - QlikView
Version 1, Dec 10, 2018

This example provides a script pattern for loading fields from multiple files, when the field names vary between files.

The same field name may use a different spelling between files.

There may also be extra fields in some files that should not be in the final data table.

Share

Multivariate Analysis Example

  Multivariate Analysis Example
Version 1, Feb 21, 2018 - Qlik Sense

This example accompanies the blog post “QDG Guru Day Inspiration“.  The example demonstrates using color encoding in a scatter chart to display two inter related dimensional series.

Click image to enlarge

Share

Sales Discovery Embed example

  Sales Discovery Embed example
Version 1, Sep 26, 2017 - QlikView

This example file accompanies the blog post

http://qlikviewcookbook.com/2017/09/quick-sense-app-in-a-page/

Share

Docs (1)

QV12 Upgrade Considerations

  QV12 Upgrade Considerations
Version 1.7, Nov 20, 2017 - QlikView

Share

Qlik Sense (4)

QS Document Analyzer

  QS Document Analyzer
Version 1, Sep 22, 2021 -

**************************************************
The QVF edition of Document Analyzer has been deprecated and  replaced by QSDA Pro.  You can download QSDA Pro from here.
**************************************************

Share

Scripting (30)

Count Days In A Transaction Using Intervalmatch

  Count Days In A Transaction Using Intervalmatch - Qlik Sense
Version 1, Dec 5, 2013

  Count Days In A Transaction Using Intervalmatch - QlikView
Version 1, Dec 5, 2013

This example demonstrates using IntervalMatch to count the number of days in a transaction. The transaction days could be calculated in the script by computing EndDate-StartDate, but using IntervalMatch allows for flexible counting of subtotals across various date dimensions such as Month or Quarter.

The example data is for a rental company that rents out equipment. The input data is:
EquipmentId, DateOut, DateIn

 

Share

Date Grouping

  Date Grouping - Qlik Sense
Version 3, Dec 5, 2013

  Date Grouping - QlikView
Version 2, Dec 5, 2013

Assigning dates to temporal groups such as “Yesterday”.

This example demonstrates assigning dates to named range groups, such as “Today” or “This Month”. Note that a Date may belong to more than one group. For example, today’s date belongs to “Today”, “This Week”, “This Month”, “Last Two Months” etc.

V2 update adds group named “Next Workday”.

Share

Delta Load Template

  Delta Load Template
Version 1, Apr 20, 2015 - QlikView

This example provides a template script for building a delta (incremental) load script. A delta load fetches only new or changed rows from the database.

This template can handle all three common database update scenarios — Insert, Update and Delete.

Share

Expand A Pricing Date Table

  Expand A Pricing Date Table - Qlik Sense
Version 1, Dec 5, 2013

  Expand A Pricing Date Table - QlikView
Version 1, Dec 5, 2013

This example demonstrates using the Previous() function and WHILE clause to “fill in” values in a date range.

In this exercise, a transaction file provides Products, Prices, and the date the Price is effective.

The goal is to determine the Product Price for any given date.

Share

Indexing Keywords in Text

  Indexing Keywords in Text - Qlik Sense
Version 1, Dec 5, 2013

  Indexing Keywords in Text - QlikView
Version 1, Dec 5, 2013

This example indexes selected keywords in text.

The example scenario is looking for specific keywords in travel reviews.

See the script for details.

Share

List Files

  List Files - Qlik Sense
Version 1, Dec 5, 2013

  List Files - QlikView
Version 1, Dec 5, 2013

This example lists information about disk files using the FileList() and DirList() script functions in conjunction with the FOR EACH statement.

A pattern and subroutine is defined to process multiple directories.

Share

Load From FieldValues

  Load From FieldValues - Qlik Sense
Version 1, Oct 22, 2015

  Load From FieldValues - QlikView
Version 2, Oct 22, 2015

This example demonstrates how to use existing FieldValues as a data source. This can be an very fast technique for finding the Min of Max value of a field.

Share

Load Input File Containing Multiple Record Types

  Load Input File Containing Multiple Record Types - Qlik Sense
Version 1, Dec 5, 2013

  Load Input File Containing Multiple Record Types - QlikView
Version 1, Dec 5, 2013

Load and Merge data from an input file that contains multiple record types.

The test file has a phone number id record followed by some call detail records for that phone number and a total charges row. We want to associate the phone number with the detail rows.

 

Share

Load Users and Groups from Active Directory

  Load Users and Groups from Active Directory - Qlik Sense
Version 1, Dec 5, 2013

  Load Users and Groups from Active Directory - QlikView
Version 1, Dec 5, 2013

LOAD Users and Groups from Active Directory using AdsDSO.

This version uses no Macros.

See the script source for details and setup for your AD domain.

Share

Loading Non Standard Numeric Signs

  Loading Non Standard Numeric Signs - QlikView
Version 1, Dec 6, 2019

  Loading Non Standard Numeric Signs - Qlik Sense
Version 1, Dec 6, 2019

This receipe demonstrates how to read numbers that have non-standard number signing formats, for example a trailing “-” to indicate negative numbers or a keyword like “DEBIT”.

    102-
    400 DEBIT

Both should be interpreted as negative numbers.

    -102
    -400

Share

Loading Varying Column Names

  Loading Varying Column Names - Qlik Sense
Version 1, Dec 10, 2018

  Loading Varying Column Names - QlikView
Version 1, Dec 10, 2018

This example provides a script pattern for loading fields from multiple files, when the field names vary between files.

The same field name may use a different spelling between files.

There may also be extra fields in some files that should not be in the final data table.

Share

Mapping Class Labels

  Mapping Class Labels
Version 1, Dec 5, 2013 - QlikView

Using MAPPING to change the label assigned to Class() values.

Share

Mapping Substrings

  Mapping Substrings
Version 1, Dec 5, 2013 - QlikView

This example demonstrates using MAPPING tables and the MapSubstring() function to replace characters in loaded strings.

Share

Mapping With Wildcards

  Mapping With Wildcards - Qlik Sense
Version 2, Oct 30, 2018

  Mapping With Wildcards - QlikView
Version 2, Oct 30, 2018

Mapping values using a table with wildcards.

Sometimes you want to use QV mapping to group values. The QV MAPPING LOAD / MAP USING / APPLYMAP is excellent for this. However, sometimes you have many different values to map and a comprehensive map is not available. This sample presents a technique where wildcard characters may be used in the mapping table to simplify the map definition.

Share

Perfmon QVD

  Perfmon QVD
Version 1, Feb 5, 2014 - QlikView

This is a generalized script to created a QVD file from Windows Performance Monitor csv file(s).

The script will discover and create a QVD field for every Perfmon Counter found in the csv files. Dimension Fields for DateTime, Group and Instance will be created as well.

Click image to enlarge

Share

Regular Expression Pattern Matching

  Regular Expression Pattern Matching
Version 1, Dec 5, 2013 - QlikView

Qlikview script contains many powerful and useful string functions. But sometimes you want the power of Regular Expression (RegExp) pattern matching. QV does not yet provide a native Regular Expression function.

This sample uses a very simple macro that uses VBScript to provide a RegExp pattern matching function in QV script.

Share

Strip HTML From Text

  Strip HTML From Text
Version 2, Oct 29, 2015 - QlikView

Extracting content from HTML using a module script function.

The function “ExtractInnerHtml” used in the script is written in the document module using the VBScript Regular Expression object. To view or modify the function definition, open the Module Editor (Ctrl-m).

Share

Time Grouping

  Time Grouping - Qlik Sense
Version 1, Aug 13, 2023

  Time Grouping - QlikView
Version 1, Aug 13, 2023

This example demonstrates assigning times to named range groups, such as “Midnight Shift” or “Early Evening”. This facilitates making selections by meaningful names, for example selecting events that occurred during a manufacturing shift.

Note that a Time may belong to more than one group and intervals may overlap.

Share

Trailing Minus

  Trailing Minus
Version 1, Dec 5, 2013 - QlikView

Some systems produce display numbers using a trailing minus sign. Qlikview expects a leading minus sign to indicate negative numbers.

This expression is one way to read numbers with trailing minus signs. It will work with both leading and trailing minus signs.

Share

Tutorial - Using Common Date Dimensions

  Tutorial - Using Common Date Dimensions - Qlik Sense
Version 1, Dec 5, 2013

  Tutorial - Using Common Date Dimensions - QlikView
Version 1, Dec 5, 2013

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model.

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

tutorial - using common date dimensions 1

tutorial - using common date dimensions 2

 

Click image to enlarge

Share

User Defined Functions

  User Defined Functions - Qlik Sense
Version 1, Dec 5, 2013

  User Defined Functions - QlikView
Version 1, Dec 5, 2013

Example of creating a “User Defined Function” using a QV variable and parameters.

Share

Tools (16)

Copy Groups Utility

  Copy Groups Utility
Version 2, Apr 24, 2017 - QlikView

This utility copies group(s) from one QVW to another.

Updates for V2:

  • Source and Target QVWs are allowed to be the same file. In this case, copied groups will have “- Copy” appended to the group name.

 

Click image to enlarge

Share

Dev Data Browser

  Dev Data Browser - Qlik Sense
Version 3, Mar 9, 2020

  Dev Data Browser - QlikView
Version 3, Mar 9, 2020

Examples of useful charts and filters to investigate your data model during development or debugging.  Shows how to use the $Table and $Fields system fields to access the fields in your data model.

Charts can be copied as-is into any app  and should work with your data without any changes requires.

Both QlikView and Qlik Sense examples are provided.

 

 

 

Click image to enlarge

Share

Import/Export Variables Utility

  Import/Export Variables Utility
Version 1, Jun 7, 2018 - QlikView

This is a QVW utility to Import or Export variables from a QlikView Document.

Click image to enlarge

Share

Prj Tool

  Prj Tool
Version 1.1, Oct 31, 2017 - QlikView

This utility provides two functions for QlikView QVWs:

  1. Create -prj directories from a set of QVWs.
  2. Build or Re-Build QVWs from -prj directories.

This is useful for making mass changes to a set of QVWs.

Updates for V1.1
– Added check for proper macro security.
– Added DeletePrj function.

 

Share

QS Document Analyzer

  QS Document Analyzer
Version 1, Sep 22, 2021 -

**************************************************
The QVF edition of Document Analyzer has been deprecated and  replaced by QSDA Pro.  You can download QSDA Pro from here.
**************************************************

Share

QV Document Analyzer

  QV Document Analyzer
Version 3.10, May 6, 2020 - QlikView

This tool is used to analyze the structure of a qvw. The analysis focuses on what fields are used and where. This information can be to used to tune the performance of a Document or investigate the impact of data changes.

This download is packaged as a zip file that includes Document Analyzer, DA Compare Tool and DABatch script.

Changes for version 3.10

  • Fixed hang during extract when QV April 2019 SR2 or SR3.
  • Added check and warning message if “Binary Load Prohibited” is checked on for the target QVW. Binary Load is required during the Reload step. Credit Sunny Talwar.

Changes for version 3.9

  • A prj directory is no longer required to get full analysis. The prj recommendation is removed from the Main sheet.
  • Changed Per User memory calc from Type=Field State to Class=State Space.
  •  Added contribution from Marcel Hug to analyze use of Module Subs and Functions.

Changes for version 3.8

  • Added “///$bnf off” to script to fix script macro error with QV Nov 2017 release.

Changes for version 3.7

  • Add “Qlik Sense Conversion” sheet that aligns with the display of the Qlik Sense QlikView convertor. This can be useful when working with the convertor.
  • Changed Per User memory calc from Class=State Space to Type=Field State.
  • MB has been changed from 1024*1024 to 1000*1000 to align with the auto scaling of Qlik Sense.
  • Corrected error in calculation of pointer size for fields having less than 8 values.
  • For Buttons that don’t have Caption text, the Button label is loaded to the Caption field.
  • Modified Function count to reflect actual count of function, instead of number of expressions.

 

 

Click image to enlarge

Share

QVPR Analysis

  QVPR Analysis
Version 2.1, Apr 18, 2015 - QlikView

QVPR Analysis is a tool that will load a Qlikview XML Repository (QVPR) and allow you to analyze the contents.

With this app you can do Qlikview Analysis of the Reload and Distribution tasks defined on your server.

V2.1 adds QVD Creation Tasks.

 

V2 Update adds Icon Graph visualization of task flow by Ralf Becher.

Click image to enlarge

Share

QVS Editor

  QVS Editor
Version 1.2, Feb 5, 2015 - QlikView

This is a QVW used to edit external Qlikview script files — *.qvs & *.txt files — using the qlikview script editor. It’s useful in that it uses the actual script editor so you get all the features you expect – syntax, tabs, dropdowns, etc.

QVS Editor

 

Click image to enlarge

Share

Script Log Analyzer

  Script Log Analyzer - Qlik Sense
Version 1.7, Dec 12, 2019

  Script Log Analyzer - QlikView
Version 1.7, Dec 12, 2019

Script Log Analyzer will analyze a set of Qlikview and/or Qlik Sense script  logs. It’s value is in visualizing the execution duration of each script statement in a reload.

Interface is available in English,  Portuguese, German and Spanish.

The v1.7 update fixes an issue where the last operation in a failed script was not accounted for.

The Qlik Sense version was written by Clever Anjos.

Click image to enlarge

Share

Script Repository

  Script Repository
Version 1.9, Jul 18, 2016 - QlikView

This tool builds a searchable repository of QlikView scripts.

Ver 1.9 Update:  Added optional “Search Words” file to search for a list of words during reload.

Ver 1.8 Update: Added a “Version 12 Upgrade Check” sheet to highlight potential QV12 script compatibility issues.

Each QVW file in the Input directory tree is opened and the script and module texts are extracted and written to the output directory. The QVWs are opened with /nodata, so the process runs quickly.

You must respond to any macro or section access login prompts.

After Reload, the extracted script can be searched using the “Script Search” sheet.

You must grant System Access to this QVW.

Follow the instructions on the “Main” sheet to get started.

After searching for “customer”

 

Click image to enlarge

Share

Table Spinner

  Table Spinner
Version 1, Apr 27, 2017 - QlikView

A small utility to extract tables from a QVW into QVD or CSV files.

 

Click image to enlarge

Share

Visualization (28)

Alternate Total Format

  Alternate Total Format
Version 1, Dec 5, 2013 - QlikView

This example demonstrates using a different number format for chart total rows.

Alternate Total Format

Click image to enlarge

Share

Calendar Chart

  Calendar Chart
Version 1, Feb 7, 2014 - QlikView

A “Calendar Chart” that displays events and metrics in the familiar Month/Weekday page format. The chart is just a cleverly formatted pivot table.

Click image to enlarge

Share

Cell Popups In Straight Table

  Cell Popups In Straight Table
Version 1, Apr 19, 2015 - QlikView

This example demonstrates how to create hover text in Straight and Pivot Table cells.

Click image to enlarge

Share

Conditional Show of Details

  Conditional Show of Details
Version 2, Nov 15, 2015 - QlikView

This example demonstrates using the Conditional Show property to show additional Charts and Text Objects after selecting to a single item.

Revision 2 of this recipe demonstrates showing additional charts after clicking in a bar chart.

conditional show of details 1

conditional show of details 3

Click image to enlarge

Share

Control Chart

  Control Chart
Version 1, Dec 8, 2013 - QlikView

Example of a Control (Shewhart ) Chart.

Control Chart

Click image to enlarge

Share

Defer Selections Until Apply

  Defer Selections Until Apply
Version 1, Dec 4, 2015 - QlikView

There may be unusual cases where you want to the user to make multiple selections before calculating charts(s).

This example demonstrates how you can collect proposed selections in an alternate state and defer applying those selections to charts until  the user presses “Apply”.

Click image to enlarge

Share

Emphasizing Chart Lines

  Emphasizing Chart Lines
Version 1, Jan 6, 2014 - QlikView

How to emphasize specific lines in a line chart. We’ll look at changing line widths, fading colors and hiding lines.

Click image to enlarge

Share

Enter Date From To

  Enter Date From To
Version 1, Dec 9, 2013 - QlikView

This example demonstrates using an Input Box to allow the user to specify date ranges for data selection.

The Constraint property is utilized to demonstrate validating user input.

enter date from to

Click image to enlarge

Share

Focus not Filter

  Focus not Filter
Version 1, Feb 3, 2015 - QlikView

This example demonstrates various techniques to emphasize selected data while still keeping all data in view.

More information can be found in the blog post “Focus not Filter” .

Click image to enlarge

Share

Heat Table Chart

  Heat Table Chart
Version 1, Dec 5, 2013 - QlikView

Color shading or gradients can be used to indicate the relative weight of values in text charts. This can be useful to draw attention to more important values in a dense text chart.

Heat Table Chart

Click image to enlarge

Share

How to Choose an Expression

  How to Choose an Expression
Version 1, Dec 19, 2014 - QlikView

This example demonstrates a technique for improving the performance of a large document when a chart needs to choose one of several expressions based on user selected options.

Complete explanation can be found in this post “How to Choose an Expression“.

Share

Make it Touchy

  Make it Touchy
Version 1, Nov 26, 2019 - QlikView

This recipe demonstrates QlikView options to make your document “touchy”, that is, provide useful feedback when you poke something with the mouse wondering “What’s this?”

Click image to enlarge

Share

Multivariate Analysis Example

  Multivariate Analysis Example
Version 1, Feb 21, 2018 - Qlik Sense

This example accompanies the blog post “QDG Guru Day Inspiration“.  The example demonstrates using color encoding in a scatter chart to display two inter related dimensional series.

Click image to enlarge

Share

Outliers

  Outliers
Version 1.1, Dec 13, 2013 - QlikView

Using a chart expression to remove high and low outlying values from each month before calculating the average Sales. If there are only two or less days in the month, the outliers are not removed.

Click image to enlarge

Share

Pivot Table Grids

  Pivot Table Grids
Version 1, Nov 9, 2015 - QlikView

This recipe provides examples of using a Pivot Table to display data in a grid. The second dimension is dragged to the horizontal creating a cross table.

The Pivot Table provides more options than a Grid chart for representation.

 

Click image to enlarge

Share

Scoping Selections

  Scoping Selections
Version 1, Jan 25, 2016 - QlikView

This example shows how the Aggr() function can be used to narrow or widen selections from a Calculated Dimension.

More on this topic can be found in the blog post  “Scoping Selections with Aggr“.

Share

Selected Warning

  Selected Warning
Version 1, Dec 13, 2013 - QlikView

This example conditionally displays a warning message in the chart when specific values are present in the data.

Click to enlarge image

Click image to enlarge

Share

True False Buttons

  True False Buttons
Version 1, Dec 13, 2013 - QlikView

This example demonstrates several alternatives for controlling True/False switches in a qvw.

Click image to enlarge

Share

Tutorial - About Column Visibility

  Tutorial - About Column Visibility
Version 1, Apr 28, 2014 - QlikView

Chart properties provide options to suppress the display or calculation of a column.

In this tutorial we’ll explore the difference between those two options and look at some use cases.

 

Click image to enlarge

Share

Tutorial - Annotating Chart Data Points

  Tutorial - Annotating Chart Data Points
Version 2, Feb 17, 2014 - QlikView

Annotations are comments or labels placed in a chart to provide additional meaning or interpretation of chart data. Text is placed on a data point or connected visually to the point.

This tutorial will demonstrate techniques for annotating data points in Qlikview Charts.

Share

Tutorial - Continuous Axis And Multiple Scales

  Tutorial - Continuous Axis And Multiple Scales
Version 1, Dec 5, 2013 - QlikView

In this tutorial we’ll learn how to use the continuous X-axis for displaying large numbers of data points. We’ll also look at enriching a line chart with additional measures and scales.

tutorial - continuous axis and multiple scales

Click image to enlarge

Share

Using Alerts To Send Numbers Via Email

  Using Alerts To Send Numbers Via Email
Version 1, Jan 8, 2014 - QlikView

The Qlikview document Alert feature can be used to send text emails with KPIs like “daily numbers” after document reload.

This example shows how to configure an alert and craft an alert text message.

Alerts may not be used to email graphical charts or attachments. Only text may be sent.

144

Click image to enlarge

Share

 

Recipes for Qlikview Success