Developer Teaching QlikView /en Script /en

QlikView Script Sub for Dataloading

 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.0
 * @author Philipp Frenzel <>

SUB LoadSourceTableByName(TableName, Historical)

LET varTableName = TableName;

SQL SELECT * FROM spaceman.$(varTableName);
// Here we store the table as is into the filesystem 
STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableName) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);


DROP TABLE $(varTableName);


Sorry, typo in the path variable, now fixed! Enjoy the script!

Common Developer Teaching QlikView /en

Project Documentation with github and markdown

A very important step while implementing more or less complex solutions is the availability of a documentation. While I’m really a fan of version based work, I will describe a quick an simple way, how I document my projects with github and markdown.

For all of you who don’t know markdown, it’s a simple “what you mean is what you get” syntax which can be compared to LATEX. (If you don’t know this eather, pls. give a search engine a visit) 😉

So what you acutally need is:

  • GitHub Account
  • TextEditor (min.) if you are the more “what you see is what you get” person, you’ll find a lot of editors in the web, even online based


Developer Teaching QlikView /en

Lesson 11 – complex data presentation


In this lesson we get to know more complex data diagrams, which are properly suited for “intergroup” analysis.


Trellis graphs serve the same visualization with a uniform scale to visualize different categories.

Screenshot 2015-03-31 21.12.50

You can decide yourself which dimensions you want to “distinguish”.

Screenshot 2015-03-31 21.54.50

In the advanced settings you can define the quantity per column and line.

 Tables with conditioanl formatting

Keep in mind, these visualizations ONLY work with the graph type “Table”!

Screenshot 2015-03-31 21.13.02

This form of visualization has proven it’s worth “exclusively” for power-useers. Otherwise the information density would be too high to keep track.


Also ONLY possible with graph type “table”. Exception: you can also integrate these so called “mini-diagrams” in a listbox.

Screenshot 2015-03-31 21.13.28


Screenshot 2015-03-31 21.13.41


  • Build a simple Dashboard with 5 or less than 5 KPIs.
  • Look out for all the “mischiefs” and eliminate them.

We advise you to orient yourself with modern “CSS-Web-Frameworks”. For example – Microsoft Metro UI or Twitter Bootstrap.

Developer Teaching QlikView /en

Lesson 10 – Standard data presentation


In this lesson we will get to know some of the most common charts and how we can make them prettier.

Bar graph

With the help of bar graphs comparisons between individual groups of values ​​can be created quickly (quantitatively).

Screenshot 2015-03-31 21.10.22

In doing so, the orientation of the charts can create a clean representation even in long legends.

Screenshot 2015-03-31 21.17.15

Combined bar/line graph

This form of visualizations is properly suited for visualizing moving data with different units.

Screenshot 2015-03-31 21.10.34It is important to use the second axis to, for example, prevent the vanishing of the percentage display.

Screenshot 2015-03-31 21.20.45

The “position” of the formula moves to the “right” side. for more complex visualizations it is practicable to calculate the MIN and MAX values using the formula.

Line graph

Especially suited for visualizing trends and fluctuations over time.

Screenshot 2015-03-31 21.10.47

Particularly interesting is the possibility to present in thresholds.

Screenshot 2015-03-31 21.24.33

To choose how the threshold should start, select from the Drop-down next to the option “line”.

Distribution and shares

Now the pie charts, block graphs, area diagrams and mekko-diagrams come in hand.

Screenshot 2015-03-31 21.11.08

The special feature of area diagrams is that they are disguised as “line graphs”.

Screenshot 2015-03-31 21.29.53

Absolute value with origin information

Depending on the requirements, “waterfall diagrams” or “pile diagrams” could be useful.

Screenshot 2015-03-31 21.11.25

For the waterfall diagram, the detail settings of the formula must be entered.

Screenshot 2015-03-31 21.33.31

Trends – candle diagram

Particularly popular visualization of, for example, stock prizes.

Screenshot 2015-03-31 21.11.51

The following values have to be obtainable:

  • Upper
  • Top
  • Middle
  • Bottom
  • Lower

The information of where you entered and where you left has to be delivered because a coloured signal is set here.

Screenshot 2015-03-31 21.37.37

Relationship between data points

The “bubble-diagram” is particularly popular.

Screenshot 2015-03-31 21.12.19

Depending on 3 factors, a distribution can be made. For example:

  • Quantity
  • Sales
  • Contribution margin

The contribution margin gets visualized as an “area”.


  • Build a diagram of choice
  • Give the characteristic settings a detailed look
  • Set the colour values to “hard”
Developer Teaching QlikView /en

Lesson 9 – Basic pivot functions


In this lesson we are going to learn about the most important aggregation functions in detail and how to “modulate” them to our purpose. A short introduction into the set-analysis is going to show us one of the most powerful functionalities.

Pivoting made easy

To create a cross table right-click an “empty” area on the work surface and choose:

Screenshot 2015-03-30 14.44.03

Chart! Then the “Wizard” starts which allows you to create a cross table.

Screenshot 2015-03-30 14.45.11
After choosing the type, click on next.

Screenshot 2015-03-30 14.45.36

Select here, after which dimension/category the calculation of the values should happen.Screenshot 2015-03-30 14.45.56
Specify the the wanted function/formula/calculation – here as a simple Count() – Quantity.

Screenshot 2015-03-30 14.46.22

You can install multiple formulas by clicking on “add”.

Screenshot 2015-03-30 14.46.40
Our cross table now shows the quantity of the plannings per product group.

Please note that there is a big difference between “clearly” counted and counted values. To really count only unique values, use the DISTINCT command.

The most commonly used functions in practice are:






//SUM(Sales) / COUNT(Sales)

//SUM(TOTAL Sales) or SUM({1} Sales)

The last expression, which represents the first reference to the set-anlyses is especially interesting.


A set is a mathematical amount. This amount can be, for example, cut with another amount or else be excluded.


Complete coalition “*”.


Only the intersection “+”.


Only in A “-“.


Only in A or B – “-P( )” – “E( )”.


Both amounts united.

To better understand the principle, we are going to look at the following examples:

//simple sum of the Sales

//Sum of the sales in 2014
//The Dollar sign stands for the current selection
//If there is no intersection-operator, QlikView goes from a *
SUM({$<Year={"2014"}>} Sales)

//Sum of sales in 2013
SUM({$*<Year={"2013"}>} Sales)

//Sum of sales in 2013 and 2014
SUM({$*<Year={"2014","2013"}>} Sales)

//Sum of all sales less than 2013
SUM({1-<Year={"2013"}>} Sales)

//Sum of all sales without 2013 and possible current selection
SUM({1-<Year={"2013"}>*$} Sales)

//filter on 2 columns
SUM({$<Year={"2013"},Category={"Toys"}>} Sales)

//filter by variable
SUM({$<Year={"$(varName)"},Category={"Toys"}>} Sales)

//filter by function result
SUM({$<Year={"$(=year(today()))"},Category={"$(varProductCategory)"}>} Sales)


  • Create a cross-table in which you can choose the sum of the sales by CalendarObject. Tip: Work with variables.
Developer Teaching QlikView /en

Lesson 8 – Working on the User-Interface


Up until now we were almost exclusively occupied with the loading of data. Starting with this lesson, we are mainly focusing on presenting and analyzing the data.

General settings

Activating the “Design-Toolbar” is one of the most important presettings for a QlikView designer. You can manage the visibility in the menu option “View”.

Screenshot 2015-03-28 15.11.28

Overview of the control objects


The listbox helps finding, selecting and analyzing data within a certain area.

Screenshot 2015-03-28 18.26.45

The following colour code for visualization has been established by QlikTech:

Screenshot 2015-03-30 13.15.32

The background is white without selection.

Screenshot 2015-03-30 13.16.50

Once you choose a selection it turns grün, the remaining options stay white and non-choosable values turn gray.

By using the options “forward” and “backward” you can navigate within the selection history. By clicking reset, every filter gets removed.


The MultiBox is a space-saving alternative to the listbox. There is only one line per filter field.
Should only be used in “standard analyses” because it makes an association harder to sport.

Screenshot 2015-03-30 13.43.58

Slider/Calendar Object

The sliders or the calendar can be connected with numerical fields or variables.
Especially useful fo “from…to” analyses.

Screenshot 2015-03-30 13.51.29

Bookmark Object

With bookmarks you can save certain “data views”. Make use of this function, because different filters can lead to interesting discussions.

Screenshot 2015-03-30 14.19.18

Search Object

“Search Object” allows you to use one expression more than once in the same field. The results then show hits per “field”.

Screenshot 2015-03-30 14.21.57An overview of the data-presentation objects

Table Box

The TableBox – Table – is suited for performantly listing the datasets on the “lowest” level. Aggregations, formulas etc. are not possible. Use the chart-object as “table” to calculate and visualize simple or complex representations.


The Chart is the most complex type that you can use with QlikView. It allows pivoting, formatting, numerical and graphical representation of values.

Screenshot 2015-03-30 14.25.46

These types are:

  • Bar Chart – classic bar diagram
  • Line Chart
  • Bar-Line Chart – combination of the two previous diagram types
  • “Spider” – radar riagram
  • Tacho – speedometer
  • Mekko – relation adjusted quantitative data diagram
  • Scatter – single data points on a x-y scale
  • Bubbles – volume driven scatter diagram
  • Pie chart
  • Funnel diagram
  • Block Chart

The classic pivot and tables are the type:

  • Pivot Table
  • Straight Table

We are going to learn about each of the types later.


  • Create an object of every type inside of an already filled QlikView document and breathe life into it.
Developer Teaching QlikView /en

Lesson 7 – Exercises


In this lesson you are going to repeat all you learned on your own with your acquired skills and techniques.

Task 1 – Dataloader Adventure Works

  1. Create a dataloader with a connetion to the acces file – AdventureWorksSample which can be found in “Admin/Common/Libs/QVComponents/Examples/DbExtract”
  2. Qualify the datas and save them into the DataStaging (work with variables)

Screenshot 2015-03-28 15.10.09


Include the data source as an ODBC system source.

Screenshot 2015-03-28 15.31.09

Screenshot 2015-03-28 15.32.59

If you are using a 64Bit system it is important to activate the “Force32Bit” option when working with 32Bit data sources. (MS Access is 32 Bit)

Your script for this first task should look something like this:

//main rider
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

 * @author Philipp Frenzel <>
 * @version 1.0
 * @copyright Frenzel GmbH 2015
 * @depends - list of scripts that need to be executed before
 * @provides - list of output files
 * @schedule - * * * * * in the style of unix cron

//set the environment

//here we load the global config
$(include=../../COMMON/00 Config/CONF_PATHS.txt);

//at this point we integrate the qv-components

//here we are writing a comment into a LogFile
CALL Qvc.Log('Starte das Laden der AdventureWorks Access Datei','INFO');

ODBC CONNECT32 TO [AdventureWorks;DBQ=D:\qlikview\qlik-master\ADMIN\COMMON\LIBS\QVComponents\Examples\DbExtract\AdventureWorksSample.mdb];

//Load Adventures Reiter
 * @dataloader tbl_demo
 * @source OLEDB
 * @version 1.0
 * Only loads one file from the databank

SET varTableName = "Product";


SQL SELECT * FROM [$(varTableName)];


Trace "Saving to Path: $(PATH_DATASTAGING)";

STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);

Let varNoRecords = NoOfRows(varTableName);
Let msgLogFile = 'the table ' & varTableName & ' has been loaded with ' & varNoRecords &

//here we are writing a comment into a LogFile
CALL Qvc.Log(msgLogFile,'INFO');

//exit rider

If you execute the script and set all settings correctly, then the table “Product” should appear in your DEV/02 DATASTAGING – index.

Task 2 – Integration of a webdata source

There are numerous datasources on the internet, most of which can easily be integrated into QlikView. An interesting website to look around a bit is: – REST – API’s are the easiest to integrate, because they are already collecting data after a single URL-request. We are going to get some weather-informations from .

Our landig point is described here in detail:

In the end the script should start like task 1 and the loading rider like the following:

 * @dataloader tbl_demo
 * @source OLEDB
 * @version 1.0
 * Only loads a single file from the databank

SET varTableName = "WeatherData";


LOAD @1 AS WeatherStationId, 
     @2 AS Labeling, 
     @3 AS Height, 
     @4 AS Date, 
     @5 AS Time, 
     @6 AS Temperature, 
     @7 AS Unit, 
     @8 AS TimeStamp
(txt, utf8, no labels, delimiter is ';', msq);


Trace "Saving to Path: $(PATH_DATASTAGING)";

STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);

Let varNoRecords = NoOfRows(varTableName);
Let msgLogFile = 'The table ' & varTableName & ' has been loaded with' & varNoRecords &

//here we are writing a comment in a LogFile
CALL Qvc.Log(msgLogFile,'INFO');

Screenshot 2015-03-28 17.13.22

 Task 3 – Load the weatherdata of all days of the month 03

Autonomous working- 15 min time.

Developer Teaching QlikView /en

Lesson 6 – Script Tips ‘n’ Tricks


This short lesson concentrates on typical questions from practice and their possible solutions.

Marking of duplicates

In practice it often happens that “duplicates” for one information exist but you only want to consider the “newest” dataset. To prevent this problem, QlikView offers so called “interrecord-functions” within scripts. These allow to access previous or following datasets during the loadtime of a source. The solution for our problem would look like this:

//in the first step we load the tables without "change"

//afterwards we sort them after our duplicate key and e.g. a time infomration
ORDER BY %DublettenId, %Date;

//In order to prevent circular references, we remove the old table

//afterwards we compare the key and mark our dataset
    ,IF(PREVIOUS(%DublettenId) = %DublettenId,0,1) AS FLAG_VALID
RESIDENT SortedTable;

//at last we remove the table again in order to prevent circular references
DROP TABLE SortedTable;

Dissolving of “multiple” keys

In practice it often happens that a relation between 2 tables exists on multiple fields. To improve performance and to keep the datamodel “clean”, it is worth it to combine those keys with a simple trick.

//Initial situation


//we create a combined key to enhance the performance
  %CostcenterId & 'xx' & %ProductId AS %Connection,

Classification of data

ApplyMap function

Assign information based on a “map”. The following requirements on the map apply:

  • The assignment table exists of 2 columns, the first one holds the comparison value, the second one the future “class value”.
  • Both columns have to be named, although the name is not relevant later on. If you forget to name e.g. an inline-table, then the first value will never generate a hit.
  • When using the assignment table, the values of a certain field will be compared and if it leads to a hit, the assignment value will be “replaced” otherwise the original value persists.

If you are asking yourself why we don’t drag the data via Join into the datamodel, then the answer is point 3 of the just now mentioned points. A JOIN would always enlarge the table and make it impossible to “keep” the original value.

Example with the use of an inline-table:

//at first we build our inline-table
Discount Deadlines:
  Discount, Deadline
  VZ1Y, 1 Year
  VZ2Y, 2 Year
  B1Y, 1 Year

//afterwards we build a mapping table
RESIDENT Discount Deadlines;

//don't forget to delete the table
DROP TABLE Discount Deadlines;

//The card thus created can now be placed on the orders
  applymap('Discount_Mapping',Discount,Discount) AS Discount Deadline

IntervallMatch Function

Classification of values based on a lower and upper value. This function however can also be used for more complex questions like a time course analysis. To understand the principle behind the function, it is certainly easier to just use the example of a real estate agency, which wants to make a classification based on size and living space.

//at first we load the apartments
 Living space,
SQL SELECT * FROM Apartments WHERE Living space > 0;

//Loading of the sizegroups
 VonQM, BisQM, SizeGroup
 0, 10, small
 10.0001, 40, intermediate
 40.0001, 100, big
 100.0001, 99999, gigantic 

//now we assign the sizegroups with the living space
IntervalMatch(Living space)

Can also be used for events and leads to the following datamodel in the background:


Performance – Linking of moving tables

The linking of moving tables – also with different column amounts and fields – gives, as long as all key fields are “overlapped”, a big performance improvement. As long as all key fields are included it is harmless for the later drilling in detail. Should however not all key fields be overlapping it is still possible, but the drilling can lead to breaks in detail, for which one might have to “explain” themselves.

Attention: QlikView atutomatically merges tables with the same structure. Which can lead to big problems, but also creates great opportunities. I will show you how to control this behaviour with the following example

//automatic merge
LOAD a, b, c FROM Table1.csv;
LOAD a, c, b FROM Table2.csv;

//manual merge
LOAD a, b, c FROM Table1.csv;

//manual "not" merge
LOAD a, b, c FROM Table1.csv;

In the last case you should keep working and change the table because there is a high risk of your datamodel to “burst”.

Execution of a function for every line of a table

QlikView permits you to create certain functions on your own and to request them with parameters. The easiest example is the writing of a message into a Log-file. (Just concerning the technique, don’t question the use!)

//Definition of the function
SUB Qvc.Log (_msg, _level)

//here we load the initial table
FROM Table1.csv;

//how many lines the table has
LET varNoOfRecords = NoOfRows('Personen');

//creation of an empty event table
 'F' as DummyField

//start of a loop through the table
FOR ii = 0 TO $(varNoOfRecords) 

 LET varName = peek('name',$(ii),'Persons');
 Qvc.Log('Hello $(varName)');



Developer Teaching QlikView /en

Lesson 5 – Project organisation


This lesson handles the general organisation and structuring of QlikView projects. Based on our longtime experience with software development and QlikView implementations, we here show our “Best Practice”.

As easement for our own projects we provided an online template which enables the development of stable BI-solutions following our standards.

Screenshot 2015-03-28 15.02.53

Folder structure

    • COMMON
      • 00 CONFIG
      • LIBS
    • DEV
      • 01 Dataloader
      • 02 Datastaging
      • 03 Datalogic
      • 04 Datamart
    • PROD
      • << as DEV >>
    • COMMON
    • DEV
    • PROD
  • DOCS

The goal of these structures is to create a distinct line between productive and development systems – without an additional testserver. Only if all logics work with test structures, a migration to a productive environment can happen.

Working with variables

To develop a “company solution” it is useful to work with global variables, which everyone can use. This is going to work like the following:

//defining a welcome variable

SET varHello = "Welcome!";
LET varUser = OSUser();

TRACE "$(varHello) $(varUser) :)";

To award global variables, QlikView offers the possibility to include external TXT-files into the current script and to execute them “right here, right now” via “include”.

The difference between LET and SET variables is the evaluation of the expression behind the “equal” symbol. LET immediately “calculates” the expression while SET saves the expression for use later on.

//implementation of external sources


If variables have been defined in “load_external_file.txt” then they are now available.

Models for script blocks

Here are some exemplary script blocks which are recommended to integrate into every QlikView script.

 * @author Philipp Frenzel <>
 * @version 1.0
 * @copyright Frenzel GmbH 2015
 * @depends - list of scripts that need to be executed before
 * @provides - list of output files
 * @schedule - * * * * * in the style of unix cron

//set the environment

//here we load the global config
$(include=../../COMMON/00 Config/CONF_PATHS.txt);

Please keep in mind that depending in which environment you are situated and also that “include” has to be controlled from external sources. The debugger helps with this by showing external sources in seperated riders.

 * @dataloader tbl_demo
 * @source OLEDB
 * @version 1.0

SET varTableName = "tbl_demo";


SQL SELECT * FROM [$(varTableName)];


STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

QlikView Components

For the completion of periodic patterns, the QlikView veteran Rob Wunderlich wrote a library which eases exactly these patterns. In our projecttemplate we permanently update it and we recommend you to use it as well.

This is how you integrate the library:

//at this point we integrate the qv-components

To help with this topic, QVWs, a comprehensive documentation, have been provided. We now get to know a few of the useful routines:

//at this point we write a comment into a LogFile
CALL Qvc.Log('Here is our information',['level']);

//level options: INFO, WARNING, ERROR - default *
//a bit complex but still useful - a "standard" calendar

CALL Qvc.Calendar (vMindate, vMaxdate, ['CalendarTableName'], ['FieldPrefix'], [FirstMonth]);

Take one day to get an overview of the features. In our practical experiene you are going to save much time with doing this.

In the folder Libs/QVComponents/examples are examples for all functions if you are sill unconfident with using them.


  • Download a “zip” from the project and build a “first” dataloader in the DEV index.
  • Use the include function to gain access to “global” elements


Common Developer Teaching QlikView /en

Lesson 4 – Structuring scripts and using functions


To not lose track in longer scripts, you have the possibility to structure the script. QlikView offers you to classify via index cards. Additionally we use easy functions to enrich our datamodel.

Generate an index sheet

To simplify the finding of ladelogics in the script, structure the script by creating new tabs or moving existing tabs. Try to find short but concise names for register riders to make navigation easier. It is especially useful to generate an “exit” register rider, which holds the command “EXIT SCRIPT;”. This makes it possible for complex data structures to stop “earlier” with the loading of the data and to analyze isolated logs.


Date functions

In addition to the company structure, which is almost always present in all projects, “time” is the most important main feature of any analysis.At this point, you will get to know the simplest date functions, but in later lessons we will learn how to build and integrate a “standard calendar”.

The most common date functions that can be applied to a field with date content are:

  • YEAR
  • DAY

Everyone of these functions is powerful, e.g different economic years can be displayed, or even pointers to the last full month are possible. A well thought out time axis is going to help you create reports, charts and tables later on. You use these functions as follows:

//Loading of the calculation table

LOAD CustomerId,
     YEAR(BillDate) AS Year,
     MONTH(BillDate) AS Month,
     DAY(BillDate)AS Day,
FROM Bills;

Functions and formulas for “calculations”

Apart from countless other options, QLikView also offers the possibility to calculate while loading data. This works as follows:

//Loading of the calculation table

LOAD CustomerId,
     Amount * Price * (1-Discount) AS BillsLineSum;
FROM Bills;

You obviously always have the possibility to calculate such calculations on the interface, but performance wise it is faster to calculate them while loading data – if possible.

Controlstructures during loading time

From time to time you have In some cases, you may need to apply certain rules depending on the field content. For this, it is possible to work with “IF-THEN-ELSE” functions.

// Loading of the calculation table

LOAD CutomerId,
     Amount * Price * (1-Discount) AS BillsLineSum,
     If(wildmatch(Produkt,'*Tennisb*')>0,'Tennis','Other') AS Division;
FROM Bills;

Here you can see how we compare the content of the field <Product> with the pattern “*Tennisb*” (* can be any amount of characters) and if the amount of hits is greater than 0, the division “Tennis” gets written, if not then “other”.

If you intend to refine search patterns you can set the number of charcters to be compared via “?” where each “?” represents a character.


  • Extend your script from lesson 3 to a moving table
  • Build a “small” calendar with year, month and day
  • Extend your Pivot by a breakdown by time