Search Recipes > Scripting (27)

Count Days In A Transaction Using Intervalmatch

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

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
Share

Date Grouping

  Date Grouping
Version 1, Dec 5, 2013 - QlikView

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.

Share
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
Share

Expand A Pricing Date Table

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

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
Share

Indexing Keywords in Text

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

This example indexes selected keywords in text.

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

See the script for details.

Share
Share

List Files

  List Files
Version 1, Dec 5, 2013 - QlikView

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
Share

Load From FieldValues

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

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
Share

Load Input File Containing Multiple Record Types

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

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
Share

Load Users and Groups from Active Directory

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

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
Share

Mapping Class Labels

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

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

Share
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
Share

Mapping With Wildcards

  Mapping With Wildcards
Version 2, Dec 5, 2013 - QlikView

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
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.

Share

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
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
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
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

 

Share

Click image to enlarge

Share

User Defined Functions

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

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

Share
Share

 

Recipes for Qlikview Success