Categories
Common QlikView /en

Prepare a new qlik project

Intro

If you prepare yourself for a “seeing is believing” workshop or going to start a new qlik project. Be aware that you should prepare your work environment!

System Requirements

Based upon the size of your data and the number of clients you need to “size” your server or developer workstation.

For a minimum developer workstation you should at least have:

  • 500GB HardDrive (2 Partitions 100,400)
  • >= i5 (min 2CPU)
  • 8 GB RAM

For a minimum server you should at least have:

  • 500GB HardDrive (2 Partitions 100,400)
  • >= 4 CPU (current tech)
  • 16 GB RAM

If you wonder why the server needs “more” ressources, it’s because each user will generate a little overhead working on the server!

Data Access

Depending on the customers data sources and to download little helpers, it’s helpful to have:

  • Access to the internet
  • File Access to all “text” based data sources
  • ODBC or OLEDB READ Account (we recommend to name it QVREADER)

Data Structure

People who work with the data and can deliver as much data structure knowledge as possible.

Data “Digging”

After you have access to the datasources, build an quick and “dirty” qvd datastaging layer. We normally suggest to do this in our qlik project template – so work is usable for production later. Take a look at the data loading “FIRST 10000” records from each datasourse and build a quick star schema.

Categories
travel /en

Whale and Dolphin Watch – La Gomera

Where to go?

If you ever had the wish to watch whales and dolphins in real “free” live, than one of the many places to go can be La Gomera, one of the tiny islands in the canary group.

Screenshot 2015-04-14 09.42.08

Depending on the time of the year, you can see different whales and/or dolphins. One of the “whale watching” companies has a detailed log about what has been seen and when (http://oceanogomera.blogspot.de/)! I wrote an tiny qlikview app, which can be downloaded here, so you can make yourself a quick impression:)

What you can see?

  • Pilotwale – Pilot whales – Calderones tropicales – Globicéphales
  • Fleckendelfine – Atlantic Spotted Dolphins – Delfines moteados – Dauphins tachetés de l’Atlantique
  • Brydewal – Bryde’s whale – Rorcual tropical- Rorqual tropical
  • Grosse Tümmler – Bottlenose dolphins – Delfines mulares – Grands dauphins
  • Gewöhnliche Delfine – Common Dolphins – Delfines comunes – Dauphins communs

What we have seen!

We went out by boat 4 times during our one week stay! 😉 We choose YANI – http://www.excursionesyani.es/index.php/en – as they had a glass bottom boat and was kind of big and same time small enough to ride with! The whole team was super friendly and tried to answer our questions as good as possible!

Here is what we saw:

 

Categories
QlikView /en QlikView GUI /en Tips n Tricks /en

QlikView – Color Blend by Dimension Value

Overview

Sometimes you wanna define a “color schema” by the values of a dimension. The easiest way is to generate a mapping table where each dimensionvalue gets an color assigned.

Downloads

First load the color table as CSV from here and save them into the same folder where you wanna create and run the QlikView Worksheet:

Sample

//Load ColorTable as IS
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
 Red, 
 Green, 
 Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

//first init variables
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

ColorTable:
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************************** //
// Dynamic Variables by Frenzel GmbH - COPY 2013 www.frenzel.net  //
// AUTHOR Philipp Frenzel <philipp@frenzel.net                    //
// ************************************************************** //

//Get the number of available rows in the rights table
LET varIntegerNoVariables = NoOfRows('ColorTable');

TRACE Number of Dynamic Color Vars: $(varIntegerNoVariables);
 
//Lets build the dynamic variables
FOR DatenbankIter = 0 TO $(varIntegerNoVariables) - 1
 
 	//here we build the logic for the color coding
	LET curVariableName = peek('ColorName',DatenbankIter,'ColorTable');
	LET col$(curVariableName)= 'rgb(' & peek('Red',DatenbankIter,'ColorTable') &',' & peek('Green',DatenbankIter,'ColorTable') &','& peek('Blue',DatenbankIter,'ColorTable') &')';
	
NEXT;

DROP TABLE ColorTable;

// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //


//then we assign the values to a dimension mapping
//create the dimension mapping
Map_Color:
MAPPING
LOAD * INLINE [
 YourFieldname, Color
 a, colCadetBlue
 b, colCadetBlue1
 c, colCadetBlue2
 d, colCadetBlue3
 e, colCadetBlue4
];

//load you data into the application

Testdimension:
LOAD * INLINE [
 YourFieldname
 a
 b
 c
 d
 e
];

//assign the colors
Dimension:
NoConcatenate LOAD
 *
 ,applymap('Map_Color',YourFieldname,'colBlue') AS ColorName
RESIDENT Testdimension;

DROP TABLE Testdimension;

If you wanna update the colors “online” just replace the first load with:

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[http://www.beeye.org/wp-content/plugins/download-monitor/download.php?id=3]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq, no eof);

After this we create a bar chart with the YourFieldname as Dimension and 1 as Formula:

Screenshot 2015-04-02 12.55.30

To color it as we wanted to, we will edit the background formula for the formula as here:

Screenshot 2015-04-02 14.26.18Which leads us to the following result:

Screenshot 2015-04-02 14.27.15

Categories
Developer Teaching QlikView /en

Lesson 11 – complex data presentation

Overview

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

Trellis

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.

 Sparklines

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

Tachometer

Screenshot 2015-03-31 21.13.41

Exercises

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

Categories
Developer Teaching QlikView /en

Lesson 10 – Standard data presentation

Overview

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

Exercises

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

Lesson 9 – Basic pivot functions

 Overview

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)

//AVG(Sales)


//MAX(Sales)


//MIN(Sales)


//COUNT(Sales)


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

Set-analyses

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

220px-Set_subsetAofB.svg

Complete coalition “*”.

220px-Venn0001.svg

Only the intersection “+”.

220px-Venn0100.svg

Only in A “-“.

220px-Venn0110.svg

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

220px-Venn0111.svg

Both amounts united.

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

//simple sum of the Sales
SUM(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)

Exercises

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

Lesson 8 – Working on the User-Interface

Overview

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

ListBox

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.

MultiBox

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.

Chart

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.

Exercises

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

Lesson 7 – Exercises

Overview

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 <philipp@frenzel.net>
 * @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
SET _ENV = "DEV";
SET _DEBUG = 1;

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

//at this point we integrate the qv-components
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/Qvc.qvs);
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/language/qvc_language_GE.qvs);

//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";

QUALIFY "*";

$(varTableName):
LOAD 
    *;
SQL SELECT * FROM [$(varTableName)];

UNQUALIFY "*";

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

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

IF _DEBUG = 0 THEN
  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);
END IF;

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
EXIT SCRIPT;

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: http://www.programmableweb.com – 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 www.data.gv.at .

Our landig point is described here in detail: http://at-wetter.tk/index.php?men=api

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

QUALIFY "*";

$(varTableName):
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
FROM
[http://at-wetter.tk/api/v1/t/2015-03-01/2]
(txt, utf8, no labels, delimiter is ';', msq);

UNQUALIFY "*";

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

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

IF _DEBUG = 0 THEN
  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);
END IF;

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.

Categories
Developer Teaching QlikView /en

Lesson 6 – Script Tips ‘n’ Tricks

Overview

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"
RawTable:
LOAD 
    *
FROM [$(PATH_DATASTAGING)TestSource.qvd];

//afterwards we sort them after our duplicate key and e.g. a time infomration
SortedTable:
NOCONCATENATE LOAD
    *
RESIDENT RawTable
ORDER BY %DublettenId, %Date;

//In order to prevent circular references, we remove the old table
DROP TABLE RawTable;

//afterwards we compare the key and mark our dataset
FinalTable:
NOCONCATENATE LOAD
    *
    ,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
Table1:
LOAD
 %CostcenterId,
 %ProductId,
 ...
RESIDENT Table1;

Table2:
LOAD
 %CostcenterId,
 %ProductId,
 ...
RESIDENT Table2;

//we create a combined key to enhance the performance
Table1:
LOAD
  %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:
LOAD * INLINE
[
  Discount, Deadline
  VZ1Y, 1 Year
  VZ2Y, 2 Year
  B1Y, 1 Year
];

//afterwards we build a mapping table
Discount_Mapping:
MAPPING LOAD
  Discount,
  Deadline
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
Orders:
LOAD 
  *,
  applymap('Discount_Mapping',Discount,Discount) AS Discount Deadline
;
SQL SELECT * FROM Orders;

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
Apartments:
LOAD 
 ApartmentId,
 Living space,
 Postcode;
SQL SELECT * FROM Apartments WHERE Living space > 0;

//Loading of the sizegroups
Sizegroups:
LOAD * INLINE
[
 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
IVMatch:
IntervalMatch(Living space)
LOAD DISTINCT VonQM, BisQM RESIDENT Sizegroups;

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

Intervalmatch

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;
CONCATENATE LOAD a, c FROM Table2.csv;

//manual "not" merge
LOAD a, b, c FROM Table1.csv;
NOCONCATENATE LOAD a, b, c FROM Table2.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)
 ...
END SUB;

//here we load the initial table
Persons:
LOAD 
  id,
  name
FROM Table1.csv;

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

//creation of an empty event table
LOAD 
 'F' as DummyField
AUTOGENERATE (1);

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

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

NEXT;

 

Categories
Developer Teaching QlikView /en

Lesson 5 – Project organisation

Overview

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.

https://github.com/FrenzelGmbH/qlik

Screenshot 2015-03-28 15.02.53

Folder structure

  • ADMIN
    • COMMON
      • 00 CONFIG
      • LIBS
    • DEV
      • 01 Dataloader
      • 02 Datastaging
      • 03 Datalogic
      • 04 Datamart
    • PROD
      • << as DEV >>
  • ANALYSIS
    • COMMON
    • DEV
    • PROD
  • CONSOLE
    • MAINTANANCE
  • DEPLOYMENT
  • DOCS
  • VERSIONING

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

$(include=load_external_file.txt);

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 <philipp@frenzel.net>
 * @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
SET _ENV = "DEV";
SET _DEBUG = 1;

//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";

QUALIFY "*";

$(varTableName):
LOAD 
    *;
SQL SELECT * FROM [$(varTableName)];

UNQUALIFY "*";

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
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/Qvc.qvs);
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/language/qvc_language_GE.qvs);

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.

Exercises

  • 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