But …

If you just start straightforward and try to load Bootstrap’s CSS (which can be downloaded from here), you’ll not be that happy because something definitely unwanted happens. The styling of Qlik Sense completely crashes because of CSS conflicts respectively Bootstrap’s CSS is overruling some of Qlik Sense’ CSS styles:

Solution

But there’s an easy solution for this problem available. What we basically have to do is to “scope” Bootstrap’s style only to our extension and not to the rest of the document, so something like this

<!-- Bootstrap styles do not work -->

<div class="bootstrap_inside">

	<!-- Here Bootstrap styles are working -->
	<button class="btn btn-success">A Bootstrap Button</button>	

</div>

<!-- Bootstrap styles do not work anymore -->

There is already a W3C proposal available to solve this problem, which allows you to create scoped CSS by adding the `scoped` attribute to a style element (read more here). Unfortunately this solution is – frankly speaking – not widely supported.

But you can achieve the same result with standard CSS:

.bootstrap_inside { }

.bootstrap_inside .btn {

	/* now the class btn only works within the class bootstrap_inside */

}

Now it’s certainly not a practical solution to change the entire CSS library of Bootstrap manually. Just too much work and would also break if there are Bootstrap updates. Fortunately Bootstrap uses Less to generate the CSS, a CSS pre-processor which helps you to write cleaner CSS definitions.

Step by step instruction

The following instructions assume that you are familiar with the basics of Node.js, Bower and Grunt (or Gulp). All the steps demonstrated below can also be achieved manually or with other tools, but this just seems to be the easiest way for me.

• Create a folder (we call it BootstrapExtension)
• Inside the folder install Bootstrap using Bower:

  bower install bootstrap
  

• You’ll see a folder bower_compoents inside the folder BootstrapExtension. If you look into \BootstrapExtension\bower_components\bootstrap\less you’ll find all .less based styles of Bootstrap. The file bootstrap.less is the entry point for generating the entire Bootstrap CSS.
• Create another folder called “less” where we put our main .less file (_root.less)
• Your folder structure should then look like this
  
  
• Then we have to add our bootstrap_inside class:

  .bootstrap_inside {}
  

• Nothing exciting so far but within this class we can now also include the main file from bootstrap. By doing so all the Bootstrap CSS definitions will then be “scoped” within .bootstrap_inside, so instead of generating .btn our less file will then generate .bootstrap_inside .btn {}

  .bootstrap_inside {
  	@import "./../bower_components/less/bootstrap.less";
  }
  

Generating the CSS output

Now we can use Grunt for generating the CSS based on the .less files with just a few simple steps

• Install Grunt

  npm install grunt --save-dev
  

• Install the Grunt plugin to compile LESS files to CSS (grunt-contrib-less)

  npm install grunt-contrib-less --save-dev
  

• Next we have to create a file with our Grunt tasks (Gruntfile.js)
• Create the file in the root of BootstrapExtension and paste the following code to it:

  'use strict';
  
  module.exports = function(grunt) {
  
    grunt.initConfig({
      less: {
  	  bootstrap: {
  	  	files: {
  		  "./output/scoped-bootstrap.css": "./less/_root.less"
  	  	}	
  	  }
  	}
    });
  
    grunt.loadNpmTasks('grunt-contrib-less');
  
    grunt.registerTask('default', ['less']);
  
  };
  

• Finally run the command grunt in the command line and you’ll see a folder output appearing containing a file named scoped-bootstrap.css with the generated style
• The last step is easy, reference the generated scope-bootstrap.css file in your extension and use it as follows:

  paint: function ( $element /*, layout*/ ) {
  
  	$element.empty();
  
  	var $noBootstrap = $( document.createElement( 'div' ) );
  	$noBootstrap.html( '<button>Should not be bootstrap style</button><br/><br/>' );
  	$element.append( $noBootstrap );
  
  	var $bootstrapStyle = $( document.createElement( 'div' ) );
  	$bootstrapStyle.addClass( 'bootstrap_inside' );
  	$bootstrapStyle.html( '<button class="btn btn-success">Should be bootstrap style</button><br/>' );
  	$element.append( $bootstrapStyle );
  
  }
  

The output:

Improvements

You can certainly do some improvements to this basic concept:

• Instead of using the entire Bootstrap CSS just go to bootstrap.less and comment imports (and therefore components) which are not needed
• You can integrate the build process explained above into your workflow how you create extensions (e.g. by using some of the best practices shown in Yeoman Generator for Qlik Sense Extensions)

References

• Sample visualization extension which demonstrates what happens if you don’t scope Bootstrap’s CSS
• Example solution how to solve the problem (as demonstrated above)

Documentation & Download: github.com/stefanwalther/qsFunnel

As of now the following media types are supported:

• (External) Images
• Videos
• Web sites

Screenshots

Roadmap

I am planning to support more media types, but I need your feedback! What are the media types you’d like to see supported by this visualization extension for Qlik Sense? So far these are the ideas I am working on

• Vimeo support
• YouTube support
• Markdown
• Inline Html, e.g. displaying some HTML formatted text from a database

Download & Documentation

The project is published at GitHub: https://github.com/stefanwalther/qsMediaBox

Download it on github.com/stefanwalther/qsCalendarHeatmap

You can download this Visualization Extension from GitHub.

Project Site: https://github.com/QlikDev/generator-qsExtension

Improvement to WebPageViewer2 (v1.0.2)

There is now a new property called “Prevent reloading if Url does not change” which exactly does what many of you requested: Prevent reloading the Url if it does not change …

WebPageViewer2: New Property "Prevent Reloading if Url does not change"

Download Latest Version + Documentation

• Documentation and Repository
• Latest WebPageViewer2.qar file
• License (changed from MS-PL to MIT)
• Note: I have moved the repository from CodePlex to GitHub

When I first saw it I thought that it would be quite easy to bring this chart also to the QlikView world. But after some research I realized that Slopegrahps have some drawbacks which are quite tricky to solve.

I have found quite a dozen of nice implementations out there but none of them was really satisfying me. So this little project was a perfect playground for me to understand challenges when creating chart-visualizations better. It really was the first project where I did not only adapt existing visualizations based on D3 to be used in QlikView, instead I created a new implementation of Slopegraphs in D3 (by combining several existing approaches).

Here is the result:

D3SlopeGraph QlikView Extension

Some Theory about Slopegraphs

Edward Tufte states: “Slopegraphs compare changes over time for a list of nouns located on an ordinal or interval scale” and he also first published and example in his book “The Visual Display of Quantitative Information” in 1983:

Slopegraph Example by Edward Tufte

Advantages and Scenarios for Using SlopeGraph

• Slopegraphs are very minimal visualizations, there is no unnecessary information
• It is easy to capture changes over time
• The same type of data is shown on the left and right side using the same units of measurement
• “Any time you’d use a line chart to show a progression of univariate data among multiple actors over time, you might have a good candidate for a SlopeGraph” (source)

Drawback

The most important and relevant drawback of using Slopegraphs is that if you have a lot of dimensions with similar values the visualization becomes messy very quickly (see some screenshots below demonstrating this).

My Approach for Slopegrahps

To overcome the above described drawback I have chosen a combination of several approaches based on the default visualization:

Default Visulization

Handling Conflicting Label
In the above displayed example you’ll see that three dimensions on the left side share the same value for 2012. Instead of displaying them upon each other the dimensions are shown one below the other.

Emphasizing Dimensions
If you hover a dimension, the current dimension will be emphasized / highlighted

Highlighting hovered dimensions

Displaying a Summary
While hovering a dimension a tooltip will be displayed with a short summary. This is especially useful if you have a lot of dimensions with little movement from one to the other time period:

ToolTip for hovered dimensions

Text Fadout for Colliding Labels
But there is still the issue that if you have a lot of dimensions with similar values that they will collide:

Label Collisions

There are many suggestions how this can be solved, I have chosen an interactive one where conflicting labels will be faded out if you hover a dimension:

Conflicting Labels - Resolution

Still to Many Dimensions & Messy Visualization
There are situation where all the above described solutions are not sufficient. Especially if you are using a lot of dimensions this can happen.

Messy result because of conflicting labels

There are two possible solutions:

• Use a different chart type
• Increase either the size of the chart object (extension) or increase the size of the plotting area. By doing so you’ll see a vertical scroll-bar, but certainly, the user has to scroll …

Same data as above but with a larger plotting area (+ scroll-bars)

Conclusion

In my opinion it is really worth giving this type of chart a try (and I would not be surprised if we’ll see Slopegraphs much more often in the future). But this type of chart is also not a “generalist”, usage and configuration has to be considered with caution.

Download Extension, Demo + Source Code

The solution called “D3SlopeGraph QlikView Extension” is published under MIT-license and free to use (but still, I am happy to hear your feedback!!!)

• Documentation and Repository
• Download of D3SlopeGraph QlikView Extension (.qar file)
• Demo QlikView application using D3SlopeGraph
• Some instructions how to install and deploy QlikView Extensions can be found here
• Licensing Information

Issues, Bugs, Wishes

If you have any issues please post them here.

Additional Articles on the Web

• Slopegraphs for comparing gradients: Slopegraph theory and practice, (Dec 2013) by Edward Tufte
• Edward Tufte’s “Slopegraphs”, an article by Charlie Park
• A Slopegraph Update, by Charlie Park

The data passed to the extension is displayed in a diverging color scale. The values are visualized as colored cells per day. Days are arranged into columns by week, then grouped by month and years.

Screenshots

Example 1 - Displaying Historical Dow Jones Data

Example 2 - Visualizing U.S. Commercial Flights

D3CalendarView Extension - Configuration Dialog

Usage Scenarios

There are several scenarios where using this extension seems to be a helpful kind of visualization:

• Comparing changes of stock exchange prices
• Making out of stock trends visible
• etc.

Download, Configuration & Source Code

The source code, a demo-application and the .qar files are published on GitHub, where you will also find a short description how to configure the extension.

GitHub-Repository to post issues, fork the source-code, etc.:

• https://github.com/stefanwalther/QlikView-Extension-D3CalendarView

For a long time I was thinking that maybe I am just developing solutions which are not used/needed by customers and partners, but the last months have shown me that the reality is different:

• I – quite often – see my solutions (posted on this blog and on CodePlex and GitHub) in real world scenarios
• And furthermore my prototypes are taken and improved, but I never receive any feedback. That would be OK, but I would really expect that at least some customers or partners post their improvements by applying patches or improvements to CodePlex or GitHub … Unfortunately this has only happened once – today.

Can you imagine why there is no real open-source culture in the entire QlikView community?

Sure QlikCommunity is great and a lot of code-samples are posted there, but you can rarely find ready-to-go open-source solutions out there and furthermore I really miss these projects where many developers are working on the same code, inspiring each other – I only know a few QlikView related open-source initiatives:

• Rob Wunderlich has initated the QlikView Compenents project
• Rikard Braathen has published some very useful solutions on GitHub, like for example the qv-user-manager and the qv-affinity-configurator
• Brian Munz has recently announced that he will put some solutions on GitHub, for example the svgReader-Extension for QlikView 11
• Ralf Becher has published some content on GitHub including some QlikView related Gists
• Thomas Friebel – a QlikTech colleague in DACH – also started to publish some extensions on GitHub
• And there is me on CodePlex and GitHub

Are there other project out there which I do not know?
If you have a look at all the projects mentioned above there is little or no contribution by other developers beside the author (except the QlikView Components project).

Wouldn’t it be nice, if …

• … there were a lot more open-source projects out there with a vital community of QlikView developers?
• … some discussions on QlikCommunity result in useful code-pieces posted as Gists (or similar formats)?
• … you were able to use the resulting solutions in your daily life as a QlikView user/developer/consultant?
• … we were able to bring our know-how together to create astonishing solutions
• … we do not need to re-invent the wheel in many cases?

My Contribution

I can only talk for myself and how I am planning to improve the situation in the future:

• I am planing to move all my content from CodePlex to GitHub because GitHub seems to support participation of other developers much better.
• I am going to upload all my prototypes on GitHub, not only projects which are ready-to-use but also alpha versions of several prototypes.
• I will be happy to receive code-requests, patches and so on and integrate them into the main-line of my solutions.

Hmmm, really looking forward to reading your ideas and opinions regarding open-source development in the QlikView world.

OK, it’s now time to stop with theory and start coding. We’ll create our first “Hello World” extension.
The easiest way to start with your first QlikView Extension is to create the required extension code where QlikView expects extensions to be:

In Windows 7/8 QlikView is loading extensions from the following folder:

C:\Users\%USER%\AppData\Local\QlikTech\QlikView\Extensions\Objects

Step 1 – Creating the Necessary Files

1. First create a folder named “ET-HelloWorld” (ET stands for Extension Tutorial).
   C:\Users\%USER%\AppData\Local\QlikTech\QlikView\Extensions\Objects\ET-HelloWorld
2. Create two empty files within this folder, one called “Definition.xml” the other “Script.js”

The Definition.xml file is the main file where QlikView searches for some meta-information of your QlikView extension. We start with a basic set of information:

<?xml version="1.0" encoding="utf-8" ?>
<ExtensionObject  Path="ET-HelloWorld" 
                  Label="Hello World" 
                  Description="Hello World Extension" 
                  Type="object">

</ExtensionObject>

If QlikView loads this file the following information is set:

• The path (folder) of our extension is set to “ET-HelloWorld”.
• Label and Description is set to “Hello World” and “Hello World Extension”, we will recognize these values in a few minutes when we are adding the extension to a QlikView Document.
• Finally we have defined that we are aiming to create an Object Extension by setting the “Type” attribute to “object”.

Now open the Script.js file and type/paste the following code into it:

Qv.AddExtension("ET-HelloWorld",
        function () {

            // Set the extension object's inner Html
            this.Element.innerHTML = 'Hello World';

        });

If this piece of code is loaded by QlikView an extension will be made available in QlikView Desktop and the inner Html of the extension element will be filled with “Hello World”.

Test Your Hello World Extension

Now let’s add the newly created extension to a new QlikView Document:

1. Open QlikView Desktop
2. Create a new QlikView document and save it
3. Switch to WebView
4. Click somewhere in the sheet and activate the context-menu (right mouse click)
5. Navigate to “New Sheet Object”
6. Switch to the accordion panel “Extension Objects”
7. You should now see a window very similar as the one below with at least having your “Hello World” extension in the list of available objects:

Hello World Extension in your list of extensions

8. Drag & Drop the “Hello World” extension somewhere to your sheet.
9. You will see the result of your “Hello World” extension:

The resulting Hello World extension

Congratulations, you have created your first QlikView Object Extension!

The Anatomy of a QlikView Extension

It’s now time to understand the anatomy of a QlikView extension. There are at least, as you have realized in your Hello-World example, two files required for an extension to work property:

• Definition.xml
• Script.js

But there are other files too, so here’s the list of all possible elements of an extension:

In the next chapter we’ll concentrate on improving the experience of our Hello-World example.

The Concept of QlikView Extensions

The concept of QlikView Extensions has been introduced with QlikView 10 and improved with QlikView 11+.

QlikView Extensions are designed to work both in the QlikView Ajax Client and QlikView Desktop (if you turn WebView on).

Business Cases

The use of Extensions is extremely broad and can be used as solutions to a number of different scenarios.
Add to documents where the following are considerations

1. A specialized or bespoke data visualization is required
2. A unique look and feel is desired
3. A visualization or user interface element is required that is not part of standard QlikView.
4. A communication from within the client with external processes or web services is desired
5. An integration of external web content / web services should be implemented

Object and Document Extensions

There are two different types of QlikView Extensions, Object Extensions and Document Extensions.

QlikView Object Extensions represent as single visualization object within a QlikView document, very similar to the standard objects (like a straight-table, a chart, a text object, etc.) QlikView provides. In comparison to a standard object an extension developer has the freedom to develop how the entire object should look like.

QlikView Document Extensions allow you to customize an entire QlikView Document, for example by using a Document Extension you could achieve that the entire document is not aligned to the left side of your browser but centered instead (see “Center Align Document Extension” by Brian Munz).

Extensions are developed by using standard web-technologies like JavaScript, Html and CSS but you could also use Java, Flash or Silverlight (even if these technologies become less and less important nowadays).

Limitations of QlikView Extensions

There are some limitations which cannot be achieved using QlikView Extensions:

• You are not able/allowed to modify neither the data model nor the data of a QlikView Application
• QlikView Extensions cannot be used in reports

Using Existing Extensions – How to Install

Before we start to develop our own QlikView Extensions let’s have a look into how existing extensions will be “installed” and used.
If you open QlikView Desktop having a fresh install you’ll see that there are no QlikView Extensions installed by default:

1. Open QlikView Desktop
2. Create a new blank QlikView document
3. Switch to WebView (View > Turn on/off WebView or click the WebView in the Design toolbar)
   
4. Right click anywhere in the sheet and select “New Sheet Object”

You’ll see a list of standard QlikView objects, but if you select the “Extension Objects” pane in the accordion you’ll notice that the list is empty:

So let’s “install” some existing extensions:

1. If you switch to the folder C:\%ProgramFilesFolder\QlikView\Examples\Extensions\ in Windows Explorer you’ll find a file called “Extension Examples.qar”
2. Double-click the file
3. QlikView will open and you will see the following dialog, which confirms that 5 extensions have been installed successfully

Going back to the QlikView document you’ll notice that there are now 5 extensions available (if not, press the key F5 to refresh).

Now drag and drop the “Web Page Viewer” extension onto your sheet. This is a very simple extension which displays a web page within the current sheet of your QlikView document.

By default you’ll see a detail of http://www.qlikview.com because this is the default URL set for this extension. Now right-click again somewhere on the extension object’s area and you’ll see the extension’s property dialog:

Beside the default properties (which can be found in the panes “Presentation”, “Caption” and “Option”) you can now set the URL of the web page. Note that the value can be defined by either just entering a text or by using an expression, which is the case in the above shown screenshot.
As soon as you have changed the URL and closed the property dialog you’ll notice that the content of the “Web Page Viewer” extension object will change immediately.

Looking Under the Hood

You are maybe asking yourself what exactly happened when you clicked on the .qar file and what type of file this is.

A .qar file (stands for QlikView Archive File) is just a .zip file with the extension .qar. If you rename a .qar file’s extension to .zip and unzip it you’ll find files (and maybe folders) in the extraction result. Here’s the entire content of the WebPageViewer.qar extension:

QlikView is exactly doing the same as described above, unzipping the file and copying the content to a folder where QlikView is expecting all extensions to be. The previously “installed” extensions can be found under

C:\Users\%USER%\AppData\Local\QlikTech\QlikView\Extensions\

For further information about how to install and deploy QlikView extension both for QlikView Desktop and QlikView server please have a look at the article I have written some time ago.

In the next chapter we will finally start coding and creating our first “Hello World” extension.

"D3 Sequences Sunbirst" Extension

Beginning with QlikView 10 I have started to develop QlikView Extensions. To be honest, starting to work with this concept of creating plugins for QlikView was really hard. At this time only little documentation and only a few examples were available.

But fortunately time has changed. Many QlikView customers and partners have started to create astonishing QlikView Extensions and the secrets behind Extensions have been discovered, bit by bit …

Especially in the last year I have created several dozens of QlikView Extensions and so I have been asked nearly every week if I could provide a step by step tutorial for creating extensions.
So here is it, my tutorial for all QlikView enthusiasts and hopefully the starting point for some great QlikView Extensions in the future.

About the Tutorial

Working through the tutorial you will

• Understand the concept behind QlikView Extensions.
• Be able to use existing QlikView Extensions.
• Learn to develop QlikView Extensions from scratch.
• Learn how you can use external libraries (e.g. charting libraries) within QlikView Extensions.
• Learn how you can communicate with external web services.

Required Know How

For being able to follow the chapters of this tutorial some basic knowledge of the following technologies is required:

• You should know how to create and configure QlikView objects using the “Web View” (simulates QlikView Ajax client in QlikView Desktop)
• A basic understanding of basic web technologies like Html and CSS
• A robust understanding of JavaScript (see here for some hints where to start)
• Some basic knowledge of the famous jQuery framework for JavaScript

Prerequisites

For developing QlikView Extensions you’ll need the following software components:

• QlikView Desktop (version 11 SR2 or higher), QlikView Personal edition is fine to start
• A simple text editor, notepad.exe is technically spoken enough, but Notepad++ or a similar software is more convenient
  (I am personally used to develop nearly everything in MS Visual Studio for years now, so I am also using MS Visual Studio for QlikView Extensions. If you are like me, I have created an article explaining how I setup my environment for MS Visual Studio).

That’s it for the beginning, in the more advanced chapters I’ll mention if I rely on additional complementary software tools.

Code Examples

For every chapter I have created code samples, so you’ll be able to start with each chapter with the same code basis as I did when writing this tutorial.

You’ll find the code samples in a GitHub project dedicated to only this tutorial. If you know how to use GitHub, just check out the repository, otherwise you can still just download a zip-file containing all chapters.

Table of Contents

You’ll find an extra page here at qlikblog.at dedicated to this tutorial where I’ll update the table of contents and also publish additional links.

I have developed this extension some months ago and it is already used by quite a lot of customers and partners who are using this extension for example for monitors in a production line.

Download + Documentation:

https://github.com/stefanwalther/QlikView-Extension-AutoRefresh

Issues

Please post issues and feature requests on the project’s issues page.

This results into a basic bullet chart but without a description and sub-title of the KPI, furthermore if you want to add a series of KPIs you have to create and configure several instances of the “Bullet Chart” extension.

While this kind of visualization (based on a concept created by Stephen Few) is quite famous this extension here offers some more functionality based on a piece of code I have found on bl.ocks.org.

Screenshots of the D3BulletCharts Extension

Adding the D3BulletCharts extension:

The D3BulletCharts extension in action:

Configuration:

Compatibility

This QlikView Object Extension is only developed and tested with QlikView 11 SR2 or higher.

Download

Source Code, .qar file and a sample application can be downloaded from GitHub.
The demo application also includes a sample how the data has to be structured in the QlikView data model to be used by this extension.

Issues

Please post issues with the extension on GitHub.

Motivation

From a performance and manageability perspective it absolutely makes sense to segment your data, to split up different analysis aspects into several applications. This is the basis of QlikView’s App approach.

But at the same time it is often needed to combine results from several QlikView applications within a single dashboard. QlikView does not deliver that functionality out of the box, but fortunately integration capabilities are very strong in QlikView, so we’d just need to bring some existing integration concepts together to create an Executive Dashboard with charts / figures from multiple QlikView applications.

The Approach

The easiest approach for placing different objects from multiple QlikView objects onto a single user interface / dashboard is probably just creating a web page and using QlikView’s Mashup capabilities (see the previously posted article and the collection of examples I have published some weeks ago).

My approach for achieving the desired result was to use two different integration techniques:

1. QlikView Mashups to reference QlikView objects from several QlikView documents
2. Using the WebPageViewer extension (which is installed as one of the example extensions when installing QlikView Desktop)
3. Putting several instances of the WebPageViewer extension on one sheet and reference external objects
4. While referencing the external objects use some parameters which should be passed to these external objects to pre-select dimensions which are common in all external documents (e.g. time dimension, region, etc.)

Unfortunately this approach does not work out of the box because there is a (very little) bug in the WebPageViewer extension which does not allow to place this extension several times on a single sheet, that’s why I have created a clone of the WebPageViewer extension (called “WebPageViewer 2”) which provides a bugfix and some other minor functional additions to the extension.

The Solution & Some Screenshots

Using this modified extension it is now possible to place as many external instances of the extension onto a single sheet:

Screenshot Executive Dashboard

Limited Interactivity

Unfortunately interactivity in this dashboard is only supported partially using this approach:

• If you change the year (field of the host document) in above displayed dashboard, the charts will refresh to show only the values for the selected years, so this works fine
• But unfortunately I did not find an easy way to support selections within the charts and bring these selections back to the host document. So I completely prevented interaction with these charts. This is defined in the extension’s properties. If the user clicks somewhere in one of the WebPageViewer2 extensions, he will see a message that interactivity is not supported. So these charts are some kind of only “read-only”.

Message displayed if user clicks somewhere in the chart

WebPageViewer2: Property Dialog to Prevent Interaction

Benefits of this Approach

This approach can be used very dynamically, so you could also reference documents depending on selected values in your host document. This especially makes sense if you segment your data using QlikView Publisher’s “Loop and reduce” functionality:

Reference Objects Dynamically based on Selection in Host Document

Downloads

• Executive Dashboard – Sample QlikView Application:
  Download example
• “Web Page Viewer 2” Extension:

Download from https://github.com/stefanwalther/QlikView_Extension_WebPageViewer2

Purpose of this QlikView Object Extension

The purpose of this QlikView extension is easy to describe: Integrate an existing web page into an existing QlikView application by just defining the Url of the web page.

Bugfix & New Feature:

Bugfix for multiple instances of WebPageViewer on one sheet
The standard WebPageViewer extension does not allow to place multiple instances of the extension on one sheet. I have fixed this in WebPageViewer2.

New Feature: “Prevent Interaction”
If this option is ticked the user will not be able to interact with the web page loaded in the extension (e.g. clicking on links, etc.
I will describe in one of my next posts a scenario why I needed this new functionality for a concrete project.

Screenshots

WebPageViewer2 Extension - Adding the Extension

WebPageViewer2 Extension - Property Dialog

Download

The extension (.qar file) can be downloaded from https://github.com/stefanwalther/QlikView_Extension_WebPageViewer2/

Setup

Follow these steps to customize your QlikView AccessPoint:

1. Make a backup of the index.htm (which can be found at C:\Program Files\QlikView\Web)
2. Add the below posted code to the header of the index.htm (after the jQuery include) and before the end of the html head tag
3. Save the index.htm

<!-- Begin of customization for pre selecting categories in AccessPoint -->
<script type="text/javascript">
$(function () {

var cSelectedCategory = 'SelectedCategory';

function preSelectCategory() {
	var selectedCategory = unescape(getQueryString(cSelectedCategory));
	if (selectedCategory) {
		selectedCategory = (selectedCategory == 'All') ? '' : selectedCategory;
		//console.log("PreSelect Category based on QueryString: " + selectedCategory)
		$('#Category').val(selectedCategory).trigger('change');
	}
}

// copied from: http://stackoverflow.com/questions/3788125/jquery-querystring
function getQueryString(key) {
	var re = new RegExp('(?:\\?|&)' + key + '=(.*?)(?=&|$)', 'gi');
	var r = [], m;
	while ((m = re.exec(document.location.search)) != null) r.push(m[1]);
	return r;
}

// Calls
setTimeout(preSelectCategory, 100);
//console.log(ap);

});
</script>
<!-- End of customization for pre selecting categories in AccessPoint -->

Usage

Now you can passed the category which should be pre-selected via the QueryString:

Example for pre-selecting the category “Sales”:
http://localhost/qlikview/index.htm?SelectedCategory=Sales

Another example for pre-selecting the category “Sales Applications”:
http://localhost/qlikview/index.htm?Sales%20Applications

Note: %20 is the encoded character for a white space

Compatibility

I have tested this solution for the following browsers:

• Chrome 27
• Firefox 21
• Internet Explorer 8, 9, 10

Support

In my opinion the QMS API is the most beautiful API QlikView offers (probably because it is one of the newest ones …). But at the same time I only see a few partner solutions available using the possibilities of this API. Really wondering why!

While I am using the QMS API quite often, to be honest I have never found the time to write some very basic articles explaining the QMS API.

Basic Introduction

Fortunately my colleague Joe Bickley has found the time to write some excellent articles which are perfect starting to work with the QMS API:

• QlikView Management API – Getting Started and Examples
• QlikView Management API – #1 Setting up a Visual Studio project
• QlikView Management API – #2 Export / Add / Delete Document CALs
• QlikView Management API – #3 Export / Add / Delete Named CALs

Other Articles & Resources:

Here are some other collected resources related to the QMS API:

• QlikView Version 11 SDK
• QlikView Management Service API Reference
• QlikView Power Tools v1.1
  Some solutions in the Power Tools are based on the QMS API: “QMS API Client”, “Qv User Manager”
• Retrieve all EDX tasks from QMSAPI
  Article on qlikblog.at
• QlikView Management Service API and .Net
  Article by Jeremy Fourman on resultdata.com
• Qlikview: Server: How to monitor a folder and trigger a QVS EDX task
  Article on quickdevtips.blogspot.de

EDX Related Resources:

• Event Driven Execution (EDX) Reference
  (Article on QlikCommunity)
• Using EDX in QlikView 11 – basic example
  (Article on QlikCommunity)
• Using EDX in QlikView 11 – A Technology White Paper
  (White Paper on QlikCommunity)
• “My experience with Qlikview 11′s Event Driven execution”
  Article on cloudiview.wordpress.com

Motivation

The range of use of this chart type is certainly limited but it is perfect to visualize the transition of two KPIs over time (years).

If you are interested in seeing the extension in a live demo, visit one of my previous posts.

Screenshots

Animated Scatter Chart - Example

Download & Installation

Installation of the QlikView extension is straightforward, there is nothing special to take care of:

• Download the extension + Sample QlikView application
• Install the extension on your local computer (doubleclick the .qar file)
• Drag’n’Drop the extension within QlikView Desktop (using WebView)
• Finally deploy the extension to your server (-> detailed instruction)

Extension Configuration

There are some properties which can be set for the “D3 Animated Scatter Chart” extension (examples below are set on the sample chart above).

Data Options

Animated Scatter Chart - Data Properties

Chart Layout & Behavior

Animated Scatter Chart - Chart Layout & Behavior

Example Application

I have created a more advanced example application where you can select the measurements for the X-axis, Y-axis and the bubble size within the QlikView application using only list boxes in QlikView.

Animated Scatter Chart - Advanced Usage

The sample QlikView application can be downloaded from GitHub.

GapMinder World - Wealth & Health of Nations

After some research I found out that the guru Mike Bostok has created a clone of the above solution using D3:

Recreation in D3 of Gapminder’s Wealth & Health of Nations by Mike Bostock

So I was wondering how long it would take to bring that solution to QlikView as a QlikView Object Extension.

First results after 10 minutes

After downloading some sample data from the World Bank and using Mike Bostock’s code I got it up and running (with some dummy data) in about 10 minutes.

First results after some minutes

Working version after 2 hours

Following some best practices and taking benefit of some experiences with QlikView Extensions the next steps were completed surprisingly fast.

• Understanding how to code works
• Re-Coding it a little bit
• Parametrizing everything to be able to define all settings in the properties of the extension
• Bringing the data from QlikView to the JavaScript code
• …

My QlikView Extension was finished … nearly …

Finished after 5 hours

So, to be honest, after having the first results the “hard” and annoying work began: Writing the documentation, cleaning the code, creating a nice sample QlikView application …

The Result

The following short video demonstrates the final result:

So Why do I Tell You That?

There are several reasons why I have decided to share this experience:

• First of all it is amazing how fast terrific chart solutions can be brought to QlikView
  (Sure, you’ll need to find some existing code, but fortunately many other people have done the hard and time-consuming work …)
• I have absolutely fallen in love with the charting library D3, I love this framework, it is amazing what can be done by just using JavaScript and SVG
  I am really looking forward to having some time to create some basic charts from the scratch …
• Have a look at the examples on D3js.org and imagine that most of these charts could be brought to QlikView easily … OK, in many cases there are better ways to visualize data, but for certain scenarios …

Some Charting Examples from D3js.org

Finally …

Sure, I will post this QlikView Extensions, so that you can use it in your projects … Stay tuned.

Motivation

The Current Selections Box has some limitations which I am confronted quite often, probably because I am working in a non English speaking country (Germany).

• It is not possible to translate the field names
• The label of the two columns (“Fields” and “Values”) cannot be translated dynamically
• It is not possible to hide specific fields
  (sure, you can use a single HidePrefix in QlikView but sometimes this is not enough)
• While the Ajax Clients has some nice flat icons in the meantime, the Current Selections Box still displays some “older” 3D icons

Solution

• Motivation
• Features Overview
• Translation of Fields
• Hiding Fields
• Icon Style
• General Behavior
• Download & Installation
• Known Issues & Limitations
• Tested with
• Feedback, Bugs & Suggested Improvements

Features Overview

Translation of Fields

Fields will be translated by referencing a loosely coupled table containing the translations. It was important for me to choose this approach because this can be combined with standard best practices to enable multiple languages within a QlikView applications on a per user/selection basis.

Example for loading the translations for each field:

[qvl]
Translations:
LOAD * INLINE [
FieldName, FieldName_DE, FieldName_EN
Dim1, Dim1 (DE), Dim1 (EN)
Dim2, Dim2 (DE), Dim2 (EN)
Dim3, Dim3 (DE), Dim3 (EN)
];

Languages:
LOAD * INLINE [
Languages, LangKey
Deutsch, DE
English, EN
];
[/qvl]

Then you have to set up your app as follows:

1. Place a listbox for selecting the language on every sheet (using the field Languages).
2. Create a variable with the name vSelectedLangKey with the expression =Only(LangKey)
3. Configure the Better Current Selections Box as shown in the screenshot below

Language Configuration Properties

The Better Current Selections Box will then show the translated field names depending on the users language selection:

No Language Selected - Fallback Scenario

Language German selected - Fields are translated to German

Language English selected - Fields are translated to English

Hiding Fields

There are two options to hide fields

• Use Character Prefixes
• Hide fields explicitely by setting their field names

Use the following properties to define which field you would like to hide:

Hide Fields in the 'Better Current Selections Box'

An Example:

Hidden Fields Based on HidePrefixes and FieldNames

Icon Style

“Better Current SelectionsBox” supports different Icon Styles which can be chosen in the extension’s property dialog:

IconStyle Property

Icon Styles of the Better Current Selections Box

General Behavior

There are several options to set the general layouting behavior of the extension:

General Layouting Options

This results into several possible rendering status of the Better Current Selections Box:

Full Feature Set

Hide Values

Hide Status Icon (Selections=Green/Locked=Blue)

Hide Header

Hide Clear/Lock/Unlock Icons

Hide Clear Icon

Download & Installation

Installation of the QlikView extension easy and straightforward, there is nothing special to take care of:

1. Download the extension
2. Install the extension on your local computer (doubleclick the .qar file)
3. Drag’n’Drop the extension within QlikView Desktop (using WebView)
4. Finally deploy the extension to your server (-> detailed instruction)

Known Issues / Limitations

Unfortunately …

Unfortunately there was not “direct” way to clear/lock a field without having this field used in a Listbox on the current sheet. So I have tried several workarounds: Finally I decided to use the most generic one. If you have “Show Clear Icons” or “Show Lock/Unlock Icons” enabled you’ll need a “Standard Current Selections Box” placed on every sheet where the “Better Current Selections Box” is used. Unfortunately.

If you “forget” to place a current selections box on the every sheet where the “Better Current Selections Box” is placed the user will see the following error message when clicking on the clear/lock/unlock icon:

Some issues in QlikView Desktop Client

I have experienced some issues in the QlikView Desktop Client when clicking on the clear icons, sometimes the clear command will not be executed correctly, but in the Ajax/Mobile client I have not experiences the same issues

Further limitations

• Alternate States are not supported

Tested With

I have developed and tested this QlikView extension only for the following system:

• Windows x86, x64
• QlikView 11 SR2+, QlikView 11.2 SR1
• Browsers:: Chrome 27, Internet Explorer 9, Firefox 20

Other versions may work fine, but I cannot guarantee …

Feedback, Bugs & Suggested Improvements

Please add feedback, bugs, suggested improvements below as a comment.

Maybe one reason for that is that documentation and examples are very rare. That’s why I have created a very detailed documentation + examples which I am publishing now.

What are Mashups?

A definition from Wikipedia:

What do You Need for Mashups with QlikView?

From the license perspective “Workbench” has to be enabled on your QlikView server. So if you have a look at your LEF-file there should be one line which looks as follows:

WORKBENCH:YES;;

How Does QlikView Support Mashups?

There are three ways how one can realize mashups with QlikView document and/or objects:

• Simple document integration (using opendoc.htm)
• Simple object integration (using singleobject.htm)
• Div integration (using the Ajax Javascript library)

The Basic Idea behind Opendoc.htm and SingleObject.htm Integration

If you have installed QlikView Server the ability to create Mashups is already there! Have a look at the folder “C:\Program Files\QlikView\Server\QlikViewClients\QlikViewAjax\” and you’ll find two files which can be used easily to either integrate a document or only some QlikView objects into another website:

• opendoc.htm
• singleobject.htm

The idea behind this kind of integration is to call a Url on the QlikView server and at the same time pass some parameters which document or object you would like to integrate. The result from the QlikView server can be easily integrated into any website using an Iframe tag in Html:

<iframe src="http://localhost/QvAJAXZfc/opendoc.htm?document=Sales%20Compass.qvw" />

The Result:

A QlikView document is integrated into another website

There are several parameters available to control the output from opendoc.htm or singleobject.htm.
A detailed list of parameters and how thy can be used are available in the QlikView Mashups Microsite.

The Basic Idea behind Div Integration

Div integration goes one step further and allows a more granular and powerful integration of QlikView objects into an existing website using the QlikView JavaScript API.

To get Div Integration to work there are three steps necessary:

Step 1: Include the QlikView JavaScript API into your website

<script language="javascript" type="text/javascript" src="http://localhost/QVAJAXZfc/htc/QvAjax.js"></script>

Step 2: Initialize the API

(in this example for the document “Sales Compass”)

<script language="javascript" type="text/javascript">
   Qv.InitWorkBench({
      View: 'Sales Compass',
      Host: null
   });
</script>

Step 3: Define where QlikView objects should be shown in your Html structure

<!-- Listbox with the Id LB5732 from the document "Sales Compass" -->
<div avqview="Sales Compass" avq="object:.Document\LB5732"></div>

The Result

Several QlikView objects are integrated into an existing website

QlikView Mashups Microsite with Documentation & Examples

To make it easier to demonstrate Mashup capabilities I have created a dedicated QlikView-Mashup microsite, which should help as

• A reference
• A collection of code-samples
• A collection of ready-to-demo examples

Resources and Download

• Online Demo
• Source Code
• Instructions for Offline Use

Cheat Sheet by CSS3.com

Online cheat sheet by CSS3.com with a comparison to CSS1 and CSS2 plus a direct link to the CSS reference on W3C.org.

Online CSS Cheat Sheet by CSS3.org

CSS3 Cheat Sheet / Smashing Magazine

Excellent (but not very pretty) cheat sheet created by Smashing Magazine, available for download as PDF file:

CSS3 Cheat Sheet

CSS3 Cheat Sheet by Liquidicity

Another CSS cheat sheet with a more elegant layout (available for download as PDF file):

CSS Cheat Sheet by Liquidicity

Compatibility Cheat Sheet

Excellent website with a compatibility cheat sheet for CSS3, Html5, JavaScript and SVG.

Compatibility Tables by CanIUse.com

CSS3 Selectors Explained

This site explains CSS3 selectors in detail:

CSS3 Selectors Explained

So I thought it would be helpful to share my collection of existing extensions.

QlikView Object Extensions

• Business Related Extensions
• Charting Extensions
• Data Formatting Extensions (Grid, Html, etc.)
• Data Processing/Manipulation Extensions
• Social Media Extension
• Other Extensions

Business Related

Gantt Chart Extension Object

Excellent Gantt chart implementation which really demonstrates the power of QlikView’s extension concept.

Author: Lee Mathews
Documentation + Download: http://community.qlikview.com/docs/DOC-3427
Technology: Html, CSS, JavaScript
Namespace: lmsGanttChart

Calendar

Calendar to display events

Author: QlikTech
Download: Installed with QlikView (\Examples\Extensions\Extension Examples.qar)
Technology: Html, CSS, JavaScript
Namespace: QlikView/Examples/calendar

Charting Extensions

3D Surface Chart Extension Object

Integration of 3D Surface Plot into QlikView as chart object extension.

Author: Lee Mathews
Documentation + Download: http://community.qlikview.com/docs/DOC-3303
Technology: Html, CSS, JavaScript
Namespace: GRSurfacePlot

Bullet Chart

Author: QlikTech
Download: Installed with QlikView (\Examples\Extensions\Extension Examples.qar)
Technology: Html, CSS, JavaScript
Namespace: QlikView/Examples/bullet

Google BigQuery QlikView Extension

QlikView Extension for Google BigQuery provides instant querying capability against the Google BigQuery platform for the business user from the QlikView application’s User Interface.

Author: QlikTech
Download: http://market.qlikview.com/qlikview-extension-for-google-bigquery.html
Technology: Html, CSS, JavaScript
Namespace: QVBigQuery

PieChart Visualization for Google BigQuery

The PieChart Visualization for the QlikView Extension for Google BigQuery extends the existing extension object by providing an additional visualization along with documentation on how to develop your own.

Author: QlikTech
Download: http://market.qlikview.com/category/extension/piechart-visualization-for-google-bigquery.html
Technology: Html, CSS, JavaScript
Namespace: N/A

Navigation Bar Extension Object

“A navigation bar extension object that allows a 2 tiered menu system to be created from data in you QV app.
The look and feel of the navigation bar can be changed via a standard stylesheet file in the extension object directory.”

Author: Lee Mathews
Download: http://community.qlikview.com/docs/DOC-3702
Technology: Html, CSS, JavaScript
Namespace: navigationBar

OnOffSwitch

OnOffSwitch is a simple control which manipulates the state of a variable in a QlikView document in iPad style.

Author: Stefan Walther
Description: http://www.qlikblog.at/1851/onoffswitch-qlikview-extension/
Download + Documentation: QvExtOnOffSwitch.codeplex.com
Technology: Html, CSS, JavaScript
Namespace: OnOffSwitch

Org Chart:

An object extension to display hierarchical data based on a hierarchy in your QlikView document.

Author: QlikTech
Source: Installed with QlikView (\Examples\Extensions\Extension Examples.qar)
Technology: Html, CSS, JavaScript
Namespace: QlikView/Examples/org

Stream Chart

Namespace:
Author: Dan English (QlikTech)
Download: http://community.qlikview.com/docs/DOC-2583
Technology: Html, CSS, JavaScript
Namespace: StreamChart

Whisker Pie Chart

Pie chart very similar to the standard pie chart of QlikView but with optimized labels.

Author: QlikTech
Download: Installed with QlikView (\Examples\Extensions\Extension Examples.qar)
Technology: Html, CSS, JavaScript
Namespace: QlikView/Examples/piechartlabel

Windrose Chart

Windrose Chart implementation using the excellent Raphael library to create SVG on the fly.

Author: Lee Mathews
Documentation & Download: http://community.qlikview.com/docs/DOC-3111
Technology: Html, CSS, JavaScript, SVG
Namespace: Windrose

Data Formatting (DataGrids, Html, etc.)

(Minimalistic) HtmlTextBox Extension

This QlikView object extension allows you to integrate Html formatted content into your QlikView application (either loaded from a database or declared manually).

Author: Stefan Walther
Description: QlikTip #39: Displaying Html content within QlikView
Download: github.com/stefanwalther/QlikView_Extension_HtmlMinimalisticTextBo
Technology: Html, CSS, JavaScript
Namespace: HtmlTextBox

Embed Video in QlikView

A QlikView extension to embed videos in QlikView.
“The extension supports MP4, OGG and WebM formats. It will default to HTML5 playback with flash fallback support for legacy browsers. Since it’s HTML5 it will work in your iPad aswell!”

Author: Alexander Karlsson
Documentation + Download: http://community.qlikview.com/qlikviews/1179
Technology: Html5, CSS, JavaScript
Namespace: VideoPlayer

Word Cloud Object Extension

Author: Brian Munz
Documentation & Download: http://community.qlikview.com/thread/62947
Technology: Html, CSS, JavaScript
Namespace: WordCloud

Data Processing/Manipulation Extensions

Data2Webservice

Data2Webservice passes data from QlikView to a web service using this extension. Doing so you can process the received data on the server side to perform tasks like saving the data to a database, creating custom outputs (Excel, PowerPoint), pass the data to other processes and systems.

Author: Stefan Walther
Description: Posting Data from QlikView to Other Systems using the Ajax Client
Download: https://github.com/stefanwalther/QlikView_Extension_Data2Webservice
Technology: Html, CSS, JavaScript
Namespace: Data2Webservice

Formatting QlikView Data the Web Way

qTemplate brings custom web formatting into QlikView. Use a very simple language to define rendering templates based on Html and CSS to display any data passed to the extension. No need to develop several extension, just use the properties of this extension.

Author: Stefan Walther
Article: Formatting QlikView Data the Web Way (qTemplate Object Extension)
Download: https://github.com/stefanwalther/QlikView_Extension_qTemplate
Technology: Html, CSS, JavaScript
Namespace: qTemplate

QvDial Extension

QvDial is a visual input control giving the user the possibility to manipulate a variable value, very similar to the functionality of a slider or like a volume control on the stereo system.

QvDial

Author: Thomas Friebel
Documentation: http://blog.dijit.de/qlikview-extensions/qvdial-qlikview-extension/ (Documentation is available in German, use Google Translate to translate it to english)
Download: https://github.com/thomasfriebel/QvDial
Technology: Html, CSS, JavaScript
Namespace: QvDial

Social Media

Twitter Feed inside QlikView

This QlikView extension display live search results from Twitter within QlikView (querying for a Twitter hashtag).

Author: Alexander Karlsson
Download: http://dl.dropbox.com/u/18211954/Extensions/LiveTwitterStream.zip
Technology: Html, CSS, JavaScript
Namespace: LiveTwitterStream

Twitter Button Object Extension

This extension add a twitter button to QlikView and allows users to click it which opens up a new window with a tweet already started for them.

Author: Brian Munz
Documentation & Download: http://community.qlikview.com/thread/60962
Technology: Html, CSS, JavaScript
Namespace: twitter

Other QlikView Extensions

QvConsole

“QvConsole provides an output range for logging issues in QlikView dashboard. This is especially useful when working with the Windows client of QlikView.”

Author: Thomas Friebel
Documentation + Download: http://blog.dijit.de/qlikview-extensions/qvconsole-qlikview-extension/
Technology: Html, CSS, JavaScript
Namespace: QvConsole

Online Cheat Sheet:

I like this cheat sheet most, because you are able the select the desired jQuery version and all entries are linked with the official jQuery documentation.

Online and interactive jQuery cheat sheet

Direct access: http://oscarotero.com/jquery/

Offline Cheat Sheets:

This cheat sheet by Antonio Lupetti can be downloaded as PDF file and contains 8 pages of the most important jQuery functions.
Highly recommended to get a quick overview of the functionality of jQuery.

Offline jQuery Cheat Sheet

Download available on http://www.woorkup.com

Why Visual Studio

Maybe you are asking why I am using Visual Studio although a “normal” editor would be absolutely enough.

Why not Using Workbench?

Sure, if you are using Visual Studio, QlikView Workbench is a good starting point for developing QlikView extensions. But my experience is, that after some practicing hours you will not need QlikView Workbench anymore and there are a lot of WYSIWYG features which are not covered in QlikView Workbench, unfortunately.

My Final Setup in Visual Studio

This is the step by step guide how I set up a Visual Studio solution/project to develop a QlikView extension:

Preperation

First of all I create an empty solution:

File > New > Project > Other Project Types > Visual Studio Solutions

Create a blank Visual Studio solution

Add an empty “ASP.net web project” (in fact you could use also other project types):
File > Add > New Project > ASP.NET Empty Web Application

Create any empty project (e.g. "ASP.net Empty Web Application")

Delete all not necessary references and properties:

Delete unnecessary files

Add the basic files for your QlikView extension:

• Defintion.xml
• Icon.png (any 24×24 pixel .png file)
• Script.js

The result should look as follows:

Resulting view after adding the basic files for your QlikView Extension

Hmmmm ….

Now you are ready to develop your extension … Hmm, wait, that’s not the big deal, isn’t it?
Yes, one important point is missing: automatic deployment to the path where QlikView Desktop and QlikView Server are trying to load the extension from.

My approach: Using “Post-build events” in Visual Studio. So I am using Visual Studio to build the project (Ctrl+Shift+B) which does in fact nothing but I have created a script I am using in all QlikView extension projects:

The code below is created to work as a post build event in Visual Studio, so the code will be executed after you have successfully built your code. In fact when using only JavaScript, Html and CSS nothing happens during a build but it can be triggered easily by hitting Ctrl+Shift+B or using the menu: Build > Build %ProjectName%.

Add the code to the “Post-build event command line” text field within the project properties:

Project Properties: Post Build Events

SET LOCAL_PATH="C:\Users\swr\AppData\Local\QlikTech\QlikView\Extensions\Objects\SwitchOnOff\"
SET SERVER_PATH="D:\QV-Server\v11\Extensions\Objects\SwitchOnOff\"

REM ****************************************************************************************************************
REM COPY TO LOCAL
REM ****************************************************************************************************************
Rem Create target directory if not exists
IF NOT EXIST "%LOCAL_PATH%" (
	ECHO "Create local path: %LOCAL_PATH%"
	MKDIR "%LOCAL_PATH%"
)
REM Remove remaining files from old builds, so delete everything
DEL "%LOCAL_PATH%*.*" /S /F /Q /EXCLUDE:DynProperties.qvpp
XCOPY "$(TargetDir)*.*" "%LOCAL_PATH%" /Y /E /I

REM Delete .dll and .pdb
DEL "%LOCAL_PATH%"$(TargetFileName)
DEL "%LOCAL_PATH%"$(TargetName).pdb

REM ****************************************************************************************************************
REM COPY TO SERVER
REM ****************************************************************************************************************
REM Create target directory if not exists
IF NOT EXIST "%SERVER_PATH%" (
	ECHO "Create server path: %SERVER_PATH%"
	MKDIR %SERVER_PATH%
)
REM Remove remaining files from old builds, so delete everything
DEL "%SERVER_PATH%*.*" /S /F /Q /EXCLUDE:DynProperties.qvpp
XCOPY "$(TargetDir)*.*" "%SERVER_PATH%" /Y /E /I

REM Delete .dll and .pdb
DEL "%SERVER_PATH%"$(TargetFileName)
DEL "%SERVER_PATH%"$(TargetName).pdb

For getting this to work you have to mark all files which should be included in the build with:

• Build Action: “Content”
• Copy to Output Directory: “Copy Always”

Setting the build action for each file properly

The advantage of this approach is furthermore that I can include several other files, prototypes and examples within my Visual Studio projects which will not be included in the “Extension output” because I do not mark these files with “Build Action: Content” & “Copy to Output Directory: Copy Always”

What do you think?

What do you think? How to you set up your development environment to develop QlikView Extensions?
I’ll be happy to read about your experiences and gotchas …

So I have created a QlikView object extension which encapsulates the logic describes above, I have named this extension “OnOffSwitch”.

QlikView Extension OnOffSwitch

Features:

• Use this extension to allow users to change the value of a variable with a nice looking interface (very similar to the experience on an iPad).
• You can use this extensions multiple times on each QlikView sheet to manipulate different variables.
• No need to code anything, all properties can be set in the extension’s property dialog.
• Settings via extension’s property dialog:
• Name of the variable to be manipulated
• Label for on/off status

Download & Further Instructions

I have posted the project on github.com/stefanwalther/qvOnOffSwitch where you can find the following

• The extension itself (.qar file)
• Further instructions how to use the extension
• A QlikView example application

The evolving database landscape (copyright 451 Research)

I can also recommend to read the research report “MySQL vs. NoSQL and NewSQL: 2011-2015″

Imagine the difference:

becomes

(Images copied from http://qliktips.blogspot.de/)

Read the full article: http://qliktips.blogspot.de/2012/11/list-boxes-are-all-right.html

Scenarios

Let’s first talk about some of the scenarios where passing data from QlikView to other systems is necessary. Just some sample requirements:

• Export to a relational database management system
• Creating Excel sheets (with more logic than the standard Excel export of QlikView provides)
• Processing QlikView data with other system
• Exporting QlikView data to cloud system like salesforce.com, Amazon, etc.
• …

The Web Based Approach Using QlikView Extensions

Sure, you can realize most of the scenarios above using macros and the QlikView IE-Plugin …

But my intention was to create a prototype to demonstrate an approach which can be used for all scenarios with as less as possible modifications.

Some explanations:

• I have created a QlikView object extension which displays dimensions and expressions and offers the possibility to select data
• Then there is a button within that extension which triggers the process of posting all data displayed within the extension to a web service. The Url of that web service can be defined within the extension.
• The referenced web service receives these data and is capable processing the data to any system which offers APIs (this requires manual coding depending on the desired target system)

Data2Webservice Object Extension – Step by Step:

Follow these steps to get the Data2Webservice object extension to work:

1) Installation and preparation
Installing and dragging the Data2Webservice object extension onto your QlikView document is straightforward as described in this article. In addition you have to prepare the web service which can be referenced by the extension.
(As a starting point you can use the sample web service provided with the code of this article)

2) Define Dimensions and Expressions
Define the desired dimensions and expressions as if you were using a standard QlikView object:

3) Additional Extension properties
Finally set some properties:

A Sample Web Service:

I have created a sample web service which can be used as a starting point for working with data posted by the Data2Webservice extension.

First of all I have created some classes (in C#/.net) which should make it easier to work with the passed data table in a structured way. The screenshot below displays the class hierarchy within the sample web service:

Using this classes makes it easy to process the data as demonstrated below:

Improvements

Since this is just a prototype there are a lot of improvements which I could think of:

• Enhance the experience of the table (fixed column headers, etc.)
• Provide samples for server side processing (Excel generation, saving to a database, etc.)
• Better handling of web service return codes (in case of success AND failure)

Tested with …

I have tested the extension using:

• QlikView 11 SR2 (x64), both using the QlikView Desktop Client and QlikView Server
• Internet Explorer 9
• Chrome 22
• Firefox 15

Download

You’ll find the source code for both the extension and a sample webservice on GitHub:

https://github.com/stefanwalther/QlikView_Extension_Data2Webservice

The goal was to bring some capabilities into QlikView (using an extension) which makes formatting data as simple as possible, ideally as simple as if you format a web page.

As a reference point I took the visualization of a very complex web-page like amazon.com.

So the main question was: how to bring such complex formatting capabilities to QlikView.

The idea behind …

I have worked in web projects for many years, so I like the flexibility how one can format web-pages using only

• Html and
• CSS (Cascading Style Sheets)

So I created a simple QlikView object extension which allows to define templates (using only Html and CSS) which automatically render QlikView data. I have called this extension qTemplate, because it uses the famous and excellent JavaScript template-engine jsRender (https://github.com/BorisMoore/jsrender)

Using this approach has some very important advantages:

• You only need one extension for all your rendering/design requirements
• There is no need to develop and install several extension, just use qTemplate and any QlikView Developer (with a little bit Html- and CSS-Know-How) will be able to custom visualizations

Let’s get started …

After you have downloaded and installed the extension drag and drop the extension to your QlikView application.

First you have to define at least one dimension and one measure (the dialog is similar to the dialog of standard Qlikview objects):

This will automatically result into:

The reason for this behaviour is a default template which is defined in the “Template” section of the extension’s properties:

Let’s have a look into this template:

 
<script id="renderTemplate" type="text/x-jsrender">
<div class="simpleStyle">
{{:#index+1}}: <b>{{:#data[0].text}}</b> ({{:#data[1].value}})
</div>
</script>

Line 1 and 5 are just necessary, but line 3 is the interesting one:

• The number within the square brackets defines the index of the column (think as you were using a Straight Table object)
• Using {{:#data[0].text}} you can reference the text representation of a column
• Using {{:#data[0].data}} you can reference the data representation of a column

For more information about formatting using the jsRender-engine (which is used in the background of this extension), please visit https://github.com/BorisMoore/jsrender or have a look at the examples stored in the "templates" sub-directory of the extension.

Options for the qTemplate Extension

Having a look at the properties of the extension you’ll find the following options which can be set:

Template

You can either define a reference to a template or paste the template directly into the properties dialog. If defining a reference to a template use

• Just the name of the file (then the extension will load it from the "templates" sub-directory
• or reference to a valid Url on your server, e.g. (http://localhost/MyTemplates/template1.txt)

Style Options:

Using the section "Style Options" you can define the following:

• Reference a custom CSS file for this extension (should be located in the sub-directory "templatestyles" of your extension
• Check if horizontal/vertical scrolling should be enabled or not

Advanced Visual Formatting

Now let’s have a look at some advanced templates (the example can found in the “template” sub-directory, called “sample_bookstore.txt”):

 
<script id="renderTemplate" type="text/x-jsrender">
<div class="bookstore_authorList">
  <div class="bookstore_image">
    <img src="{{:#data[3].text}}" hspace="0" vspace="0" />
  </div>
  <div class="bookstore_bookTitle">
    {{:#data[0].text}}
  </div>
  <div class="bookstore_author">
    Author: <a href="{{:#data[2].text}}" target="_blank">
    {{:#data[1].text}}
    </a>
  </div>
  <div class="bookstore_language">
    Language: {{:#data[5].text}}
  </div>
  <div class="bookstore_openlink">
    <a href="{{:#data[4].text}}" target="_blank">Buy book on amazon.de</a>
  </div>
  <div class="bookstore_price_container">
    <span class="bookstore_price_label">Price:</span>
    <span class="bookstore_price">EUR {{:#data[6].text}}</span>
  </div>
  <div class="clearAll" />
  <hr />
</div>
</script>

… so, as you can see, it is just a little bit Html … and CSS:

Advanced Logical Formatting (Nested templates)

If you have a look at the other examples you’ll also find a demo how rendering templates can be nested (sample "sample_foreach.txt"):

  
<ul id="baseTemplateObject"></ul>  

<script id="renderTemplate" type="text/x-jsrender">
  {{for #data[0] tmpl="#innerTemplate"}}
</script>  
<script id="innerTemplate" type="text/x-jsrender">
  <li class="simpleStyle">{{:text}}</li>
</script>  

Explanation:

• By defining an Html object with the Id "baseTemplateObject" you can define your base-object, the output of the main-template (with the Id "renderTemplate") will be rendered into that object
• The main template (Id "renderTemplate") iterates through all values of the data behind the extension and will call the template with the Id "innerTemplate" for every row

This results into:

Advanced Logial Formatting (If … else)

You can also use if-then-else constructs or other advanced formatting techniques within your template. Have e.g. a look at the example “sample_if.txt” (open on GitHub). A detailed list of all rendering possibilities can be found on the demo-page of jsRender.

Tested with …

I have tested the extension using:

• QlikView 11 SR2 (x64), both using the QlikView Desktop Client and QlikView Server
• Internet Explorer 9
• Chrome 22
• Firefox 15

Known Limitations:

The amount of records available within the extension is hardcoded, change it by opening the Script.js file and change the following line to the desired amount:

 var gMaxRecordSize = 250; 

Download

The extension itself and a sample QlikView application can be downloaded on GitHub: https://github.com/stefanwalther/QlikView_Extension_qTemplate

Create your desired sub directory and create an empty Definition.xml file within that directory. I use the following content for that Definition.xml for all my extensions:

 
<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Type="object">
</ExtensionObject>

From then on you’ll be able to access the files within the newly created directory, doesn’t matter if we are dealing with a .js, .css or any other file type, e.g.:

 
Qva.LoadCSS(Qva.Remote + (Qva.Remote.indexOf('?') >= 0 ? '&' : '?') + 'public=only' + '&name=' + "Extensions/NameOfYourExtension/CSS/ExtensionStyle.css"); 

Tested with …

I have only tested this with QlikView 11 SR1 and SR2, not really sure if that also works in QlikView 10, but any feedback on compatibility with QlikView 10 is highly appreciated.

• About 300 unique QlikView developers / day are using the wizard on weekdays (!!!)
• They create about 2.5 expressions on average
• More than 50% of these users are recurring visitors

So it seems that the Set Analysis Wizard is helpful for at least some of the QlikView developers out there …

On the other hand I am really surprised that one of the features I have implemented is only used by 5% of all users. I talk about the ability to share created expressions with other developers … It took some time to implement this functionality but obviously this is not really a killer feature …

Porting the Set Analysis Wizard to a Document Extension Solution …

Having now in mind that sharing is not really necessary and helpful I am thinking about porting the whole solution to a document extension solution.

This should not be that tricky because most of the code is already based on JavaScript and would also make some essential advantages possible:

• Tight integration into the place where you are developing your applications
• Offline use
• Selecting from the fields of your current application
• Reviewing the results of your expression immediately

But …

But I am not really sure if it is (really) worth putting additional effort into this solution because I do not really know how you are using the existing wizard and especially which features you are missing, so …

… I need your help!

Please give me an idea how you are using the Set Analysis Wizard:

• How do you use the Set Analysis Wizard in your daily work as a QlikView developer?
• If you do not use it, why?
• What features are missing?
• Do you need more examples?
• Are you (really) missing the support of using “Alternate States”?
• Would it be helpful having the Set Analysis Wizard available as a document extensions?

Please use the comment section below for your feedback, which is really appreciated!

What is this “QlikView Governance Dashboard” for?

If you already know the Metadata Powertools published on QlikCommunity you’ll find some of the existing concepts in “QlikView Governance Dashboard”:

The QlikView Governance Dashboard is a free QlikView application that delivers a 360 degree view of any QlikView deployment by illustrating its efficiency and effectiveness. It allows IT professionals to discover insights about how QlikView is being used as well as providing a centralized view to metadata, data governance and data lineage.

So “QlikView Governance Dashboard” scans your entire QlikView environment and collects meta data which will be visualized in the “QlikView Governance Dashboard”:

• Information about your QlikView applications
  • Number of tables, fields, etc.
  • Structure of your QlikView applications: objects, measures, etc.
  • Analysis of the complexity of all your applications, resulting in the complexity analysis (which is my absolute favorite)
• Information about your data sources
• Information about your load tasks
• Data lineage for all your applications
• Statistics of QlikView Server (RAM usage, CPU, max VM committed and so on)

Installation & System Requirements

• Download “QlikView Governance Dashboard” from QlikMarket
• Installation is easy, just follow the installer’s instructions.
• QlikView Governance Dashboard is available for the following systems:
  • Detailed system requirements are available here

Configuration

Configuration of QlikView Governance Dashboard is absolutely easy:

Just open the application and follow the instruction within the “Configuration” tab, then click on the “Scan” button:

Some screenshots

After having installed you can explore your entire QlikView environments, below some highlights:

“QlikView Governance Dashboard – Overview”

Detailled information about a single QVW: 

Overview of the objects used within a single QlikView application:

Detailled server statistics:

My favorite section, the “Complexity Analysis” which is a perfect starting point for getting a feeling which applications should be optimized, which are the complex ones, …

Further information

• QlikView Governance Dashboard – Overview (Video on Youtube)
• Installation and configuration (Video on Youtube)
• Metadata Management with QlikView Expressor (Video on Youtube)
• QlikView Governance Dashboard – Datasheet
• QlikView Governance Dashboard – FAQ
• QlikView Governance Dashboard – System Requirements

The most interesting news on QlikView 11 SR2:

With this release also “Offline Views” or “QlikView Offline Capability” for iPad, so the ability to browse data on iPad offline, has been added to this release …

More news on that this evening soon …

I received a lot of eMails how to force this for the Ajax-Client and/or Mobile-Client, here’s the answer:

Setup for QlikView Server

Open the settings.ini file for your QlikView Server.

This file is located at

• Windows Server 2008
  C:\ProgramData\QlikTech\QlikViewServer\
• Windows Server 2003
  C:\Documents and Settings\All Users\Application Data\QlikTech\QlikViewServer\

Within the section [Settings 7] add the following line (doesn’t matter where):

SelectionStampInBIFFExport=1

Then restart the QlikView Server Windows Service.

The result

Now for all data sent to Excel this will result into:

Installing a .qar file

If you have downloaded a .qar (QlikView Archive file) it is easy to install this file for later usage within the QlikView Desktop:

Just doubleclick the file and QlikView will be started automatically showing you the confirmation dialog that the .qar file has been deployed to your local computer.

Doing so you can now switch to WebView and drag and drop the new extension to your application.

What happend in the background

A .qar file is technically spoken just a ZIP file with the extension .qar.
QlikView has unzipped the file and copied the content of the ZIP file to a directory where QlikView Desktop listens to.

This is depending on your operating system and the type of the extension (object or document extension):

Windows 7:

• C:\Users\[user]\AppData\Local\QlikTech\QlikView\Extensions\Objects
• C:\Users\[user]\AppData\Local\QlikTech\QlikView\Extensions\Document

Windows XP:

• C:\Documents and Settings\[user]\Local Settings\Application Data\QlikTech\QlikView\Extensions\Objects
• C:\Documents and Settings\[user]\Local Settings\Application Data\QlikTech\QlikView\Extensions\Document

Deployment to QlikView server

If you want to deploy your object or document extension you have to copy & paste the content of the extension to the server manually, again depending on operating system and extension type:

Windows Server 2008:

• C:\ProgramData\QlikTech\QlikViewServer\Extensions\Objects
• C:\ProgramData\QlikTech\QlikViewServer\Extensions\Document

Windows Server 2003:

• C:\Documents and Settings\All Users\Application Data\QlikTech\QlikViewServer\Extensions\Objects
• C:\Documents and Settings\All Users\Application Data\QlikTech\QlikViewServer\Extensions\Document

Alternate Installation Path for QlikView Server

If you want you can define an alternate installation path for your server extensions within the QMC (QlikView Mangement Console):

By doing so you can from then on use the following paths to copy your extensions to:

• Document Extension: %Your Alternate Extension Path%/Document/
• Object Extension: %Your Alternate Extension Path%/Objects/

Removing QlikView Extensions

Removing QlikView Extensions either from QlikView Server or QlikView Desktop is easy: Just remove the extension folder (below the paths mentioned above) and the extension is not available anymore.

By default QlikView’s TextBox object does not interpret Html content. It will just show the full Html code loaded into the TextBox object. This also does not change if you are using the WebClient (Ajax client or Mobile client).

This article shows how easy this can be achieved by creating a simple object extension.

Minimalistic HtmlTextBox Extension

I call this extension “Minimalistic HtmlTextBox Extension” because I could think of a lot of nice features which could be implemented for such a control. But for the first step let us just display Html …

After deploying the object extension to the appropriate directory create a new sheet object using the context menu in either the Ajax client or within the QlikView Desktop (using WebView):

Just drag and drop this object type to your existing sheet.
The next step is easy:

• Either enter a static string containing the Html
• or define a field in your data modell into which you have loaded your Html content from any datasource.

Example static string:


='<b>This is the header</b><br/><ul><li>Item 1</li><li>Item 2</li><li>Item 3</li></ul>'


This will result into:

Example using a field:

[qvl]
=Only(FieldWithHtml)
[/qvl]

You can certainly use more advanced formattings like the following example:

Scrolling:

If the QV object is not large enough horizontal and vertical scrollbars will be displayed automatically:

Properties:

The following properties will be copied from the QV-object to control responsible for displaying the Html content:

• height and width
• Font size, family (if not overruled by CSS within the Html code

Ideas for Improvements

There are a lot of possibilities how this could be improved:

• Stylesheets: Add a possibility to reference style sheets
• Other formatting: Support not only Html but for example also BBCode or Wiki formatting
• Security: Remove Javascript and other possible harmfull code !!!
• …

Tested on:
QlikView 11 SR1, Windows 7 x64, Internet Explorer v9, Firefox v14, Google Chrome v21

After having tested some freely available products I have chosen the open source product hMailServer (http://www.hmailserver.com/), which can be used as a lightweight local SMTP-server and is quite easy to install.

This document will guide you through the process of installing, configuring and securing hMailServer for usage on your local computer.

Installation

During the installation I have only used the standard-options available, so this should not be the tricky part.

The installation of hMailServer takes about 1-2 minutes.

Configuration

Step 1: Open the hMailServerAdministrator

After installing hMailServer open the control panel of hMailServer:

Open the "hMailServer Adminstrator" and log in with the previously defined password.

Step 2: Basic Configuration of hMailServer

First create a local domain:

Enter the desired domain name for local usage ("QTTest.info" in my example below).

Save and you’ll see the newly added domain in the left region in the control panel:

Now we add a new user and password which you should remember for later usage. Therefore click on "Accounts" in the left pane and add the user:

Screen after having added the new user :

Now we have to enable/disable the required protocols:

Then we have to bind the local SMTP-server to the local IP-address (127.0.0.1).

Therefore please go to "Settings" => "Protocols" => "SMTP":

I’d recommend to disable the "Auto-ban" feature to prevent that your local IP-address will be blocked by mistake:

Now we have to bind the protocols SMTP and POP3 to the local IP-address 127.0.0.1. This will ensure that hMailServer will only listen to the local IP-address:

Step 3: Enable Logging

For diagnostic purposes I’d recommend to enable logging of the server, which can be quite helpful:

Step 4: Testing the settings

We are finished, we can now test our setting via "QlikView Enterprise Mangement Console":

Opening the log-file will show the result of our test-mail:

You can now use the local SMTP-server to test:

• Mail functionality from Publisher/Server
• PDF Distribution via eMail
• Alerts in QlikView

Final Step: Securing the local hMailServer

Finally we have to secure the local SMTP-server:

I prefer the following settings for local testing because these settings will prevent me to send test-mails to customers or external addresses (not the domain QTTest.info) by mistake.

If you want to send to external mail-addresses, just check the option "External to external e-mail addresses".

To secure your local SMTP-server completely from being accessed by other computers than your local one, just disable everything for the IP-range "Internet" or set the "Expires" date to the past:

Receiving (locally sent) eMails via POP3

For receiving eMails either use Outlook or an additional lightweight mail-client like SeaMonkey for demonstration purposes:

For configuring the eMail-client use the following settings:

• Server-address: 127.0.0.1
• Account: swr@qttest.info
• Password: ****
• Authentication: none/anonymous

Settings of SeaMonkey (I just use this mail-client for testing and demo purposes):

So testing this in a real world scenario where you have the following EDX tasks:

EDX Task one
EDX Task two
EDx Task three

The function using the search key “X” will return just the following results:

EDX Task one
EDX Task two

So, yes, it is case sensitive and will only return the values containing exactly the key you were searching for.

So IQMS.FindEDX is:

• Case sensitive
• And it returns only task if “the name of an EDX taks contains this value” (the parameter)

But I had the requirement to retrieve ALL EDX tasks stored on QlikView Server, which can be accomplished by using the following method (C# code):

public static List<TaskInfo> GetAllEDXTaks(IQMS qmsApiClient)
{
List<TaskInfo> taskInfoList = new List<TaskInfo>();
IEnumerable<char> range = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!§$%&()=?*+#@'<>;:_-|1234567890".AsEnumerable();
char[] az = range.Select(i => (Char)i).ToArray();

foreach (var c in az)
{
    List<TaskInfo> taskResult = qmsApiClient.FindEDX(c.ToString());
    if (taskResult != null && taskResult.Count > 0)
    {
        foreach (TaskInfo r in taskResult)
        {
            if (taskInfoList.FindAll(e => e.ID == r.ID).Count <= 0)
            {
            taskInfoList.Add(r);
            }
        }
    }
}
return taskInfoList;
}

How do you choose your most secure passwords?
I just have to share this, this is brilliant:

Some thoughts about password strength

Source: xkcd.com/936/


Sum({$<Year=,Month=,CustomerCountry=P({1<CategoryName={'Confections'}>}SupplierCountry),Customer=P({1<Year={$(=max(Year)-1)}>}Customer)>}Sales)


I do normally not! And even if I would, after two weeks I cannot remember why I have created this statement like this …

That’s why I have created the “Set Analysis Wizard for QlikView”

Introducing the Set Analysis Wizard for QlikView

So let’s directly jump to the result in the “Set Analysis Wizard for QlikView”:

If you open the above mentioned expression in the wizard, you’ll see the following:

And if you click on the tab “Generated Expression (+ Comments)” you’ll see the following:

You are welcome to add/change set modifiers at any time:

So what’s happening here?

By using the “Set Analysis Wizard for QlikView” you’ll now have a tool which

• enables you creating Set Analysis Expressions in WYSIWIG mode (!!!)
• Event the most complex Set Analysis Expressions will be documented automatically
• optionally you can save the expression on the server and edit/change/enhance it at any time
• furthermore you can share the created expression with others

Step by Step Instructions

Hopefully a step by step instruction is not necessary.
I have worked hard to create a self explanatory user interface (but if I did not succeed in your opinion, please do not hesitate to tell me!)

So just give it a try:

Examples

I have added some examples which can be directly opened in the wizard:

All examples can be opened directly in the wizard

Sharing your Set Analysis Expressions

Feel free to share your results.
Just click on “Save result for later usage and sharing” and two things will happen:

1. The Set Analysis Expression will be saved on the server and you can come back later an re-edit it at any time.(The link for re-opening the Set Analysis Expression will be added to the comment of the expression automatically)
2. Furthermore you can share your expression with colleagues, in discussion forums, and so on …

Enable "Save result for later usage or sharing" and "Share Expression"

Roadmap

So far this is the first beta version.
I have not included QlikView 11 support (Alternate States) so far, but for sure, this is the next update you can expect …

Feedback, Improvements, etc.

Please give me feedback:

• Do you like the Set Analysis Wizard?
• What is missing?
• Is it easy to use?
• etc.

[qvl highlight=”7″]
Mapping_Map:
Mapping
LOAD
Field1,
Field2
FROM qvdFile.qvd (qvd)
WHERE 1=1;
[/qvl]

or

[qvl]
Mappping_Map:
Mapping
LOAD
Field1,
Field2,
1 as Field_temp
FROM qvdFile.qvd (qvd);
DROP FIELD Field_temp FROM Mapping_Map;
[/qvl]

or

just loading the fields to a temporary table and then using a RESIDENT LOAD.

Also have a look at the excellent article from Rob Wunderlich on several QVD-topics (“QVD Questions and answers”) where he also mentioned this issue.

Although I have not read any offiicial announcement on that topic a direct load from a QVD file into a mapping load is now possible in QV 10 SR2.

Just a little testing script, if you want to ensure if it really works:

[qvl]
Map:
LOAD * INLINE [
F1, F2
A, A-mapped
B, B-mapped
C, C-mapped
];
STORE Map INTO Map.qvd(qvd);

DROP TABLE Map;

// The real testing scenario starts here …
Mapping_Map:
Mapping
Load *
FROM Map.qvd (qvd);

Transactions:
Load
TransLineID,
TransID,
ApplyMap(‘Mapping_Map’, Pick(Ceil(3*Rand1),’A’,’B’,’C’),’n/a’) as Dim1,
Pick(Ceil(6*Rand1),’a’,’b’,’c’,’d’,’e’,’f’) as Dim2;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;
[/qvl]

Good News! Although there is now some need to improve our existing scripts to improve the load time by some seconds

• Part 1: Using the design mode of QlikView
• Part 2: Moving/Copying objects (this article)
• Part 3: Linking objects
• Part 4: Aligning objects
• Part 5: Sizing objects

This topics sound so easy and probably it is, but just for a complete series on working with the QlikView Designer I have to include this …

Moving/Copying objects in QlikView

First of all you can move single object by using your mouse:

Just click on the title bar of an objects and drag the object to the desired position:

Moving objects by using the mouse

If you hold the Ctrl key the object will be copied:

Copying objects by holding the Ctrl key

Copying/Moving an object can not only be done within a sheet but also to other sheets: Just drop the object onto the desired tab:

Moving or copying an object onto another sheet

Keyboard shortcuts for moving/sizing

If you want to use only the keyboard you can use the following shortcuts:

Locking objects

If you want to prevent that object can be moved/copied/sized you can lock an object by setting the following properties in the object’s properties:

Locking Objects

I thought instead of just posting these two links I could deliver more value by starting a series of articles which cover most of the tipps & hints when working with the QlikView Designer. (Sure, if you are a QlikView addict this will not really tell you something new, but maybe this series of articles is a good starting point for QlikView Beginners …)

So this series of articles cover the following topics:

• Part 1: Using the design mode of QlikView (this article)
• Part 2: Moving/Copying objects
• Part 3: Linking objects
• Part 4: Aligning objects
• Part 5: Sizing objects

Using the design mode of QlikView

First of all it is important to mention that there is a special design mode in QlikView!

You can activate this by activating the “Design Grid” (Menu “View” => “Design Grid” or by using the shortcut Ctrl + G or using the icon for changing to the design grid: ).

Doing so, you will then see the design grid (layout raster), which may help you to align objects:

The Design Grid in QlikView

For further information how to change the layout raster, please have a look at the following article: QlikTip #14: Changing the Layout Raster.
Having the Design Grid enabled now, you do not just have a layout raster available to align your objects, furthermore you’ll also see which objects are selected, which is really usefull when you are resizing, moving your objects:

Selected Objects in Design Grid Mode

You can select one or more objects by

• Clicking on the object or better clicking on the title bar …
• Holding Shift + Clicking on the desired objects to select multiple objects
• Pressing the left mouse-button and create a selection square form bottom-right to top-left (only those objects which are per 100% within the selection will be selected):

Selecting objects by creating a selection area

Last but not least it has to be mentioned that there is a very nice functionality of QlikView only available if your are in the “Design Grid” mode:

Custom Cell Formatting

As you know there are several possiblities to design rows, columns & cells in the QlikView designer, but the “Custom Formatting” functionality is probably the most convenient one:

If your are in the “Design Grid” mode and activate the context menu of a TableBox or Chart (Pivot Table or Straight Table) you’ll see an additional menu entry called ‘”Custom Format Cell”:

Calling this you’ll see a dialogue with a lot of nice and easy-to-use functionalities to format the underlaying table:

OK, that’s the first part of this series of articles, stay tuned and do not miss the other parts …

So after upgrading to QlikView 10 SR2 these maps could look like the following screenshot:

Google maps are not working anymore?

I have digged into this “problem” and the solution is quite simple:

• Just check the transparency of your dynamic background image; if this is set to 100% (like in the example above) the dynamic image will be shown, but with 100% transparency

Set the transparency of the dynamic image to 0% !!!

After changing the transparency again to 0%, the map should work again:

Google Maps integration is finally working again as expected

This article contains a summary of the most important changes.

QlikView for iPad / Ajax Touch Client

The SR2 contains the new rendering engine optimized for touch feeling with the iPad client.

Have a look a the following articles/viedos:

Introducing QlikView on iPad

A “marketing” video in QlikView on iPad

Click here to view the embedded video.

Quick Tour on QlikView on iPad

Click here to view the embedded video.

QlikView on iPad: Full Tour

Video on YouTube with detailed explanation of all new features …

Click here to view the embedded video.

QlikView Mobile White Papers

Data Sheet: QlikView on Mobile

Technical Brief: Mobile Security

Technical Brief: Mobile User Interface Design Best Practices

The Ajax Client can be localized now

The Ajax client can be now be localized. Therefore you have just to change a section in the settings.ini file for the QlikView server.

A Proxy Page has been added to QlikView WebParts & QlikView Workbench

A proxy page has been added to both QlikView Web Parts and QlikView Workbench in order to help avoid cross-side scripting issues when the AccessPoint is located on a different machine than the website hosting the QlikView Objects.

Determine the client

To determine the current client a new function called ClientPlatform() has been introduced.

QlikView Scalability – Video 1: Overview

“This video introduces the 5 part video series and provides a high-level overview of the topics associated with understanding how QlikView can scale.”

Click here to view the embedded video.

QlikView Scalability – Video 2: System Resource Usage

“This video provides a fundamental understanding of how QlikView utilizes system resources such as RAM and CPU in a typical deployment.”

Click here to view the embedded video.

QlikView Scalability – Video 3: Scaling by Data

“This video covers the topic of scaling QlikView deployments when facing increasing data volumes.”

Click here to view the embedded video.

QlikView Scalability – Video 4: Scaling by Users

“This video covers the topic of scaling QlikView deployments when facing increasing user numbers.”

Click here to view the embedded video.

QlikView Scalability – Video 5: Scaling by Application Design

“This video covers the topics of understanding QlikView’s approach to scaling the number of applications in a deployment and discusses the importance of application design as it relates to QlikView performance.”

Click here to view the embedded video.

http://blog.birchroad.net/2011/03/16/how-to-use-git-with-qlikview-10/

http://birchroad.wordpress.com/2011/03/16/how-to-use-git-with-qlikview-10/

For all who are working with Git this is really worth reading …!

Let’s assume you have the following data:

And you want to calculate the highest expression of Value_1, Value_2, etc. within your load script to get the following result:

How would you solve this?
Just use the comment section to post your ideas … I am looking forward to reviewing your solutions …

Regards
Stefan

P.S.: Ralph & Team: You are not allowed to participate in this competition because you have already received my solution for that question

This is sometimes pain and time-consuming. Therefore I have created a code which can be easily re-used and where you only have to declare which objects should be exported and the rest will be done automatically.

The scenario

Let’s assume we have a QlikView application with some objects:

Now let’s have a look how we can cover the following scenarios. In all cases I am first declaring some settings (as a VBScript array) which will be passed to the function copyObjectsToExcelSheet.

(The sample application and the source code can be downloaded at the end of this article)

The idea behind this solution

My idea was to create a functionality which can be used in most of the required scenarios:

• Adding multiple objects to a single sheet
• Adding multiple objects to multiple sheets
• Define a name of each sheet in Excel (instead of just using “Sheet1”, “Sheet2”, etc.)
• Exporting either the data of an object or exporting the object/chart as an image

Therefore we first have to create an “export definition” by using a multidimensional array in VBScript:

Dim aryExport(0,3)

Definition of the four dimensions within the array:

Sample 1: Exporting just one object and defining a name for the sheet

'// Array for export definitions
Dim aryExport(0,3)

aryExport(0,0) = "objSalesPerYearAndRegion"
aryExport(0,1) = "Sales per Region a. Year"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

This will result into:

OK, OK, not really the big effort so far But let’s have a look at further requirements …

Sample 2: More enhanced export of three objects to three different sheets

Now let’s export three different objects to three different sheets within the same Excel document:

'// Array for export definitions
Dim aryExport(2,3)

aryExport(0,0) = "objSalesPerRegion"
aryExport(0,1) = "Sales per Region"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "objTopCustomers"
aryExport(1,1) = "Top Customers"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

aryExport(2,0) = "objSalesPerYearAndRegion"
aryExport(2,1) = "Sales per Region a. Year"
aryExport(2,2) = "A1"
aryExport(2,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

Now the result is more interesting:

Sample 3: Export of multiple objects in different formats (data & image)

But now let’s try the get most out of the script:

'// ****************************************************************
'// Export of multiple objects in different formats (data & image)
'// In one case (sheet "Sales Overview") two objects are placed on
'// one sheet.
'// ****************************************************************
Dim aryExport(4,3)

aryExport(0,0) = "objSalesPerRegion"
aryExport(0,1) = "Sales Overview"
aryExport(0,2) = "A1"
aryExport(0,3) = "image"

aryExport(1,0) = "objTopCustomers"
aryExport(1,1) = "Sales Overview"
aryExport(1,2) = "H1"
aryExport(1,3) = "image"

aryExport(2,0) = "objSalesPerYearAndRegion"
aryExport(2,1) = "Sales Overview"
aryExport(2,2) = "A14"
aryExport(2,3) = "data"

aryExport(3,0) = "objTopCustomers"
aryExport(3,1) = "Top Customers"
aryExport(3,2) = "A1"
aryExport(3,3) = "image"

aryExport(4,0) = "objTopCustomers"
aryExport(4,1) = "Top Customers"
aryExport(4,2) = "A14"
aryExport(4,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

The result:

The resulting sheet 1 containing images and a data table

The resulting sheet 2 containing images and a data table

As always, I’ll be happy if you’d share your thoughts, ideas, comments, improvements with me!

This also fixes the Visual Studio bug when trying to add the QlikView OCX control which is also described in the Reference Manual of QlikView:

I have just tested this and registration of OCX now works again as expected.

Every Qvd file in QlikView has a XML header (which you can easily prove by opening any Qvd file using a standard text editor):

There are several function available for usage withing QlikView load scripts for accessing these meta data:

QvdCreateTime(filename)
QvdNoOfRecords(filename)
QvdNoOfFields(filename)
QvdFieldName(filename , fieldno)
QvdTableName(filename)

Using these functions within a sample

[qvl]
// QlikView load script
Set a = ‘D:\QlikView\Transactions.qvd’;

LET vFileSize = FileSize(a);
LET vQvdCreateTime = QvdCreateTime(a);
LET vQvdNoOfRecords = QvdNoOfRecords(a);
LET vQvdNoOfFields = QvdNoOfFields(a);
LET vQvdFieldName = QvdFieldName(a, 1);
LET vQvdTableName = QvdTableName(a);

TRACE –;
TRACE FileSize: $(vFileSize);
TRACE QvdNoOfRecords: $(vQvdNoOfRecords);
TRACE QvdNoOfFields: $(vQvdNoOfFields);
TRACE QvdFieldName(1): $(vQvdFieldName);
TRACE QvdTableName: $(vQvdTableName);
TRACE –;
[/qvl]

would result into:

Result of reading the qvd header within a QlikView load script

Certainly, for accessing these functions you have to use QlikView, but I wanted to be able to achieve the same results outside of QlikView.
So I’ve created a little .net (2.0) based library which you can use in your projects:

Class diagramm of the C# library for reading the Qvd headers

Using the class QvdMetaInfos is easy (example in C#):

foreach (FileInfo file in new DirectoryInfo(@"C:\\QlikView\").GetFiles("*.qvd"))
{
         QlikLibs.QvdMetaInfos qvdInfos = new QlikLibs.QvdMetaInfos(file.FullName);
         Console.WriteLine("File:\t\t" + qvdInfos.QvdFileName);
         Console.WriteLine("QvdNoOfRecords:\t" + qvdInfos.QvdNoOfRecords.ToString());
         Console.WriteLine("QvdNoOfFields:\t" + qvdInfos.QvdNoOfFields.ToString());
         Console.WriteLine("QvdTableName:\t" + qvdInfos.QvdTableName);
         Console.WriteLine("");
         Console.WriteLine("Fields:");

         foreach (QlikLibs.QvdField qvdField in qvdInfos.QvdFields)
         {
              Console.Write("\t\t" + qvdField.FieldName);
              Console.Write(" (" + qvdField.NumberFormat.Type + ")\n");                
         }
         System.Console.Read();
}

The result:

Result of testing the library with a console application

Imagine we have the following situation:

There is a delta-load which is loading the newest data from our database every day. Most days we only have to make an incremental load (“I”), but sometimes the database is refilled, so on these days we have to rebuild our QVD-storage completely. Therefore also previously loaded QVD-files should not be considered anymore):

Loading plan of the delta-load

So on 01.01.2011 and 04.01.2011 we have to make our full-load, all data within QVD-files on disk which a lower LoadNr than the current one should not be considered anymore …

Three different approaches:

When thinking about this requirement I have three ideas how to accomplish that:

1. Delete the files with a macro called from the load script
2. Delete the files by executing a batch statement
3. Use a pure load-script statement without giving your script system-access and empty the QVD-files within your load-statement

(Continue to read to approach 3 because I’d recommend doing it this way! :))

Var. 1) Delete the files with a macro

The idea is simple:

• Create a macro using the COM-object “Scrpting.FileSystemObject”
• Call this macro from within your load-script

Macro:

public function DeleteFile(filePath)

	Set objFSO = CreateObject("Scripting.FileSystemObject") 
	Dim retVal 'as Boolean
	
	If objFSO.FileExists(filePath) Then
		Call objFSO.DeleteFile(filePath)
		retVal = true
	Else
		Set objFSO = nothing
		retVal = false
	End If 
		
	DeleteFile = retVal
	
end function

Load-Script:
[qvl]
SET vQvdFile = ‘Sales_2011_01.qvd’;
LET fileDeleted = (DeleteFile(‘$(vQvdFile)’) = -1);

// 0 = false
// 1 = true
TRACE File deleted $(fileDeleted);
[/qvl]

To get this to work you have to lower the security-level of macro-execution and allow “System Access”:

Allow System Access for your Macro

Cons:

• You have to use macros
• You have to lower the security-level
• More complex to use

Var. 2) Delete the files by executing a batch statement

Just add the following line of codes to your load-script:
[qvl]
EXECUTE cmd.exe /C del Sales_2011_01.qvd;
[/qvl]

For getting this to work you have to enable the following option:

Allow your load-script to execute external programs

Pros:

• If you are already allowing external programs to be executed from your load-script, this is probably the easiest way!!!

Cons:

• You have to lower the security-level

Var 3) Just empty the QVD files

Surprise, surprise, this is my favorite approach:

[qvl]
// ******************************************************************
// EmptyQvd is a generic procedure which deletes all records within a
// given QVD file.
// This is especially usefull if you do not need the records within
// this file anymore but do not want to rely on macros/batch-commands
// to delete the file.
// The fields will remain within the QVD file so you will not have any
// problems doing a wildcard load like "LOAD * FROM ‘QVD_*.qvd’" …
//
// ~~
// Usage:
// Call EmptyQvd(‘Sales_2009_02.qvd’) // using a relative path
// or
// Call EmptyQvd(‘C:\QlikView\Sales_2009_02.qvd’) // using an absolute path
//
// ~
// Author: Stefan Walther
// Date: 01/31/2011
// Version 1.0
// ******************************************************************
Sub EmptyQvd(qvdFilePath)

TRACE –;
TRACE Start EmptyQvd for file $(qvdFilePath);

// First check if the qvd-file exists
if (not IsNull(QvdCreateTime(qvdFilePath))) then
TRACE … file exists …;

// Check if the Qvd-file contains more than 0 records
if(QvdNoOfRecords(qvdFilePath) > 0) then

// Get one of the fields to create the fake "WHERE EXISTS" clause
LET vFieldName = QvdFieldName(qvdFilePath,1);

// Now let’s create a random value which cannot exist within
// the first field
FakeFieldTable:
LOAD
// Create a fake-field which can never be found within
// the existing qvd
‘FakeField’ & ‘ABCDEFGHIJKLMNOPQRSTWXYZ’ &
text(round(rand())) as $(vFieldName)
AutoGenerate(1);

// Create a random table name to prevent collisions with already
// existing tables in memory
LET vTempTableName = ‘_TempTableName’ &
text(round(rand() * pow(9,10)));

// Load the existing QVD-file with and EXISTS clause which cannot
// be found within the QVD-file; so the result will be an empty
// inline table with all field definitions of the QVD-file
$(vTempTableName):
LOAD
*,
1 as loadEnabler
FROM ‘$(qvdFilePath)’ (qvd)
WHERE Exists(Dim1)
;

// Re-Store the QVD-file, just with the field definitions but
// without any data …
STORE $(vTempTableName) INTO $(qvdFilePath) (qvd);
DROP TABLE $(vTempTableName);

DROP TABLE FakeFieldTable;

end if

else
TRACE … file ‘$(qvdFilePath)’ does not exist;

end if

TRACE finished EmptyQvd;
TRACE –;

End Sub
// __________________________________________________________________
[/qvl]

Call the script within your load-script:
[qvl]
Call emptyQvd(‘.\Sales_2011_10.qvd’);
If a = 0 then
TRACE … TEST SUCCEEDED: QVD file has 0 records …;
Else
TRACE … TEST FAILED: QVD file has $(a) records …
End if
[/qvl]

Pros:

• Doing so you will not have to change the required security level for executing your load-statement
• But most important: this will not break scenarios with a wildcard load from QVDs (like “LOAD * FROM ‘Sales_*.qvd (qvd)’;”

Cons:

• Sure the file will still remain on your hard disk …

Just save copy these commands to the command-line or create a .bat-file with the contents of the following two code-blocks.

Starting the windows-services:

@echo off
REM -------------------------------------------------------
REM - File: QlikViewServer10_Start.bat
REM - Description: Start all QlikView related services (v10)
REM -------------------------------------------------------
echo Start QlikView Services
echo ======================================================

net start "Qlikview Directory Service Connector"
net start "QlikView Distribution Service"
net start "QlikView Management Service"
net start "QlikView Server"
net start "QlikView WebServer"

echo ======================================================
echo All QlikView related services have been started ...

pause

Stopping the windows-services:

@echo off
REM -------------------------------------------------------
REM - File: QlikViewServer10_Stop.bat
REM - Description: Stop all QlikView related services (v10)
REM -------------------------------------------------------
echo Stop QlikView Services
echo ======================================================

net stop "QlikView Server"
net stop "Qlikview Directory Service Connector"
net stop "QlikView Distribution Service"
net stop "QlikView Management Service"
net stop "QlikView WebServer"

echo ======================================================
echo All QlikView related services have been stopped ...

pause

Two big changes has happened in my life in the meantime:

• I have become a daddy
• I have changed my profession

Talking about being a father now would be nice but does not really fit into the topics of this blog. But talking about my new job really does!
I am proud to announce that I have joined QlikTech as an employee in Nov 2010. I am working now as a PreSales Consultant at QlikTech. This brings me to the most important point related to this blog:

I can now concentrate on Business Intelligence and QlikView by 100%. Doing so I will definitely continue to write articles on qlikblog.at. But in comparison to the past I will try to broaden the topics I write about a little bit.

My personal goal is to write about QlikView and related technologies/concepts which are often used when working on Business Intelligence projects with QlikView.

So at the moment I have the following topic map in my mind:

• QlikView, QlikView, QlikView
• QlikView Publisher/Server
• Publishing more tools for the daily work with QlikView like the “Nested if-generator tool”, which is used by visitors of qlikblog.at more than 100-times used a day (!!!!)
• QlikView APIs
• Related web technologies (At least since introduction of extension in QV know-how of web-related technologies become more and more important for QV-developers!)
• QlikView related scripting- and programming-languages (like SQL, VBScript, JavaScript, C#)
• Operating systems both on client and server side

So stay tuned and subscribe to the RSS-feed or just drop me some lines which article you would like to read here as soon as possible!

Regards
Stefan

First of all I’d like to bring the following article on “Associative Search” to your attention which has been posted in the new QlikView 10 WIKI section on community.qlikview.com.

An excerpt of the most important part of this article:

1. Search mode for any list box can be tailored to one of the following:
   1. Wildcard Search (was default behavior in previous versions)
   2. Normal
   3. Fuzzy (as version 9)
   4. Associative – ability to search this list box based on values in associated data elements
2. The standard search mode has dropped the default ‘*’ (wildcard) to frame the search string. This means a user can now simply type the words or phrases of interest. Words separated by a space are searched using a logical OR, meaning they can appear in any order in the target value. Phrases can be searched by framing the phrase in “”, forcing QlikView to find an exact string match. This brings QlikView in line with most commonly accepted search engine functionality.
3. Associative Search can be further configured so that you can limit Search to:
   1. All fields associated with the target
   2. A list of fields (built by an expression)
   3. Manually selected list of fields

So let’s now concentrate on configuring the “Associative Search”:

How to configure “Associative Search” in detail:

First go to the properties of one of your list boxes:

List Box properties to change the "Default Search Mode" to "Use Associative Search"

After doing so “Associative Search” is active for this list box.

Per default the associative search would search within all fields of your document. But you can change this behavior by going to More Search Settings:

Opening the search settings for the associative search

More Search Settings for Associative Search

Besides enabling associative search in all fields in your document there are the following options available:

Enabling associative search for a “List of fields”

You can define a list of fields built with an expression. The fields should be separated with commas (Note: this is so far not documented in the reference manual, but it works … :-)).
Example:
[qvl]
// The following expression would define Field1 & Field2 & Field3 if there is only one value for "Year" available, otherwiese just Field1
=if(getPossibleCount(Year) = 1, ‘Field1,Field2,Field3’, ‘Field1’)
[/qvl]

Sorry: While writing this article I could not find a really sensefull business case for defining the fields using an expression; but I’m sure you’ll find one

Enabling associative search for “Selected Fields”:

You will be shown the following dialog where you can define the desired fields:

Selecting some fields to be included in the associative search.

• Deleting using the “Variable Overview”
• Deleting within the load-script
• Deleting using macros

Option 1: Deleting using the “Variable Overview”

For deleting a variable using the “Variable Overview” within the windows client just follow the steps below:

1. Call the “Variable Overview” dialog (Menu => Settings => Variable Overview or Ctrl + Alt + V)
2. then select the variable you would like to delete
3. and press the button Delete

Delete a variable from the user interface

So far so good and not new to you, but how can we delete a variable within a load-script?

Option 2: Deleting within the load-script

Variables can be deleted/removed within a load-script as follows:

[qvs]
// the name of the variable is vVariable
// first option for deleting variables within the load-script
SET vVariable = ;

//second option for deleting variables within the load-script
LET vVariable = null();
[/qvs]

Note: these two options do only work under the following conditions:

• A variable must not “survive” a load-script (exist after a load-script has finished)
• the two possibilities shown above do only work for variables which have been first created within the current load-script.
• If a variables has once “survived” a script-load, you have to use again option 1 (Variable Overview) to delete a variable

Option 3: Deleting a variable using a macro

Last but not least it should be mentioned that you can also delete variable using macros.

ActiveDocument.RemoveVariable "vTestVariable1"

Note: It is not necessary to check whether the variable exists or not when calling ActiveDocument.RemoveVariable
If the variable does not exist, the function RemoveVariable does not throw an error.

This article is the translated version of the originally posted german article QlikTip #9: Löschen von Variablen.

This feature gives the end user the possibility to search for values in a listbox considering associated data to get the desired results.

For example:
Instead of directly selecting/searching for the company named “YXZ Company” within the “Company”-listbox, you can enter the search-expression “Europe” and “Vienna” and you’ll get all “XYZ Company” if this company is associated with the region “Europe” and the city “Vienna”.

I highly recommend to spend 58 seconds to watch the following video which has been published 3 days ago:

Click here to view the embedded video.

Stay tuned, in QlikTip #28 I will tell you more about how to use the feature “Associated Search” as a developer or application designer.

Nice news: QlikView 10 is generally available …

Check the official page at http://www.qlikview.com/us/landing/qv10

Availability: QlikView 10 can be viewed in action at http://demo.qlikview.com and will be available for free download shortly at http://www.qlikview.com/download

So it is time to publish a lot of new articles about QlikView 10

I have tested QlikView 10 quite intensively in the last months, so feel free to send me your questions related to new features and functionality in QlikView 10!!!

Instead of copying some objects to other sheets and having to change the properties of these objects independently you can link some objects together and change these properties only once.

All changes will be automatically shared with all other linked objects.
Linked Objects in QlikView do not follow a master/sleeve concept, so there is no master-object and other sleeve-objects which inherit their properties from the master-object.
Instead the linked objects are having equal rights and share properties like described in the reference manual of QlikView 10:

When two or more objects are linked they share all properties with the exception of size, position and display state (minimized, normal, maximized).
When you change the properties of one object the change is immediately reflected in the other linked objects. Linked objects may reside on the same sheet or on different sheets.

How to create a linked object:

There are two different approaches creating a linked object:

“Classic” way:

Use the context menu of an object and select Copy to Clipboard => Object or go to Menu => Object and Copy to Clipboard and Object; both can also be achieved with the shortcut Ctrl+C
Then use the command Menu => Object => Paste Sheet Object as Link

Drag’n’Drop:

Drag an object onto another sheet while pressing Ctrl+Shift, then the following icon will appear indicating that the object will be placed on the desired sheet as a linked object:

Icon shown during drag'n'drop and pressing Ctrl+Shift

How to change (shared) properties of linked objects:

As written in the reference manual:

If you are making any changes on the shared properties, these changes will be automatically and immediately reflected in other linked objects.

So there is nothing special to do. You just have to remember, that size, position and display state are not shared automatically.

How to change (unshared) properties of linked objects:

If you are making some changes to the unshared properties (size, position and display state) you have to synchronize theses settings to all other objects manually. But also this is quite easy:

Adjust the position of linked objects via context menu

Use the context menu of the object from where all other linked objects should inherit the size, position and display state and use the command Adjust Position of Linked Objects.

Deleting objects:

If you are deleting a linked object you will get the following message:

Message shown when trying to delete a linked object

So you can decide whether to delete only the selected object or all linked objects.

How to unlink linked objects:

Unlinking an object is easy:
Just use the context-menu of the object and go to Linked Objects => Unlink This object

Unlink the selected object via context menu

Some remarks on the linked objects’ IDs:

Linked objects share the same object ID. If an object will be unlinked a new object ID will be assigned to the unlinked object.

Linked objects in real life:

I personally like the approach of creating a sheet which is my template and where I design the structure of the interface placing all objects there which should also be visible on other sheets.
When creating a new sheet, I just copy all objects on my template-sheet and paste them as linked objects on the newly created sheet:

Template sheet with some objects to be linked on other sheets

When making changes I just have to go on the template sheet, making the changes there and they will be automatically made on all other linked objects:

An application using a lot of linked objects

If I have to make changes regarding position and size of the objects I am also doing this on the template sheet and then I am using the Adjust Position of Linked Objects functionality (as described above).
In production environment I just hide the “Source Objects” sheet with a “Show Sheet condition”.

For me this is the perfect solution which really meets my requirements.

Impact on existing applications and macros:

Good news! Since the linked objects share the same object ID there is no impact on existing applications and macros!

Conclusion:

Give Linked Objects a try! They will make your life as application designer in QlikView much easier!
I really, really like this feature

What do you think?

My favourite way of organizing the QlikView projects in the file system

Some hints:

• In 0_Scripts all included scripts are saved
• 1_Resources includes all Excel-files, settings files and other resources (eg. images, pictures, CSV-files, …) to be used within the load-script.
• Under 2_QvdGenerators I place all my QlikView files generating QVD-files. All the QVD-files are placed within the subfolder “QVD” (and sometimes certainly subfolders of QVD)
• In 3_DataModell I save my QlikView-file generating the datamodell, putting all QVD-files together to a nice datamodell.
• In 4_Application the final result of my application is located, just loading the data modell with a BINARY LOAD and adding the user-interface to the available data.

Important notes and advantages of this approach

• This structure does only work, if you have relative paths enabled. If doing so you can easily copy the development solution to the production folder or even to the production-system on another server.
• Obviously this is just a standard structure, in smaller projects some of the folders would not be used, in larger projects I would add some sub-folders, but in general the structure remains the same …
• When working in large projects (e.g. applications with 50 GB) you can easily work with a smaller datamodell (only a subset of the final dataset) and create your user interface without having to save each time a 50GB large file … for doing so just change the BINARY LOAD in your development environment and change it again to the large file in PRODUCTION ENVIRONMENT …
• You can easily put this structure to your production environment just via copy&paste

How do you structure your QlikView projects?

I am interested in your personal way of organizing the projects.
Please use the comment functionality below to post your ideas.

The default layout-raster in QlikView's design mode

Personally I prefer to work with a smaller raster:

A user defined smaller layout-raster in design mode

This setting can be found (saved as a user setting) at:

Menu => Settings => User Settings (or Ctrl+Alt+U) and then select tab Design where you can easily adopt the desired Design Grid Settings (I prefer a Line Distance of 5mm):

Set the raster in QlikView's user settings.

The following code example demonstrates how this can be achieved:

[qvl]
Customers:
LOAD * INLINE [
CustomerId, PostalCode, City
1, 1010, Vienna
2, 8020, Graz
3, 6020, Innsbruck
4, 9020, Klagenfurt
];

// Now we would like to know if the field ‘<code>Country</code>’ exists in the table ‘<code>Customers</code>’
LET X = FieldNumber(‘Country’,’Customers’);

if $(X) = 0 then
// field does not exist
TRACE field ‘country’ does not exist;
/*
add your code here …
*/
else
// field exists
TRACE field ‘country’ exists;
/*
add your code here …
*/
end if
[/qvl]

Explanation:
The function FieldNumber returns 0 if a field does not exist.

Simplification for better reuse:

By using a “user defined function” (UDF) we can simplify the code above for easier reuse:

[qvl highlight=”13″]
// ******************************************************************
// Checks if a field exists.
// ~
// Parameters:
// 1st parameter: Name of the field
// 2nd parameter: Name of the table
// Usage:
// LET a = $(udfFieldExists(‘CountryId’,’Countries’));
// TRACE field exists: $(a);
// Return value:
// Will return true (-1) if the field exists, otherwise false (0).
// ******************************************************************
SET udfFieldExists = (FieldNumber($1,$2) <> 0);

// Now call the function as follows:
LET a = $(udfFieldExists(‘Country’,’Customers’));
LET b = $(udfFieldExists(‘CustomerId’,’Customers’));
TRACE udf-result (Country): $(a);
TRACE udf-result (CustomerId): $(b);

[/qvl]

The original german version of this article can be found here.

The desired result:

The result: Different line-styles within a single chart

How can we manage that in QlikView?

First I added a field named IsForecast in my demo-application to differentiate between actual and planning figures:

[qvl]
LOAD * INLINE [
Customer, Sales, IsForecast, Period
CustomerA, 20, 0, 1
CustomerA, 30, 1, 2
CustomerA, 35, 1, 3
CustomerA, 38, 1, 4
CustomerA, 45, 1, 5
CustomerA, 46, 1, 6
CustomerA, 43, 1, 7
CustomerA, 69, 1, 8
];
[/qvl]

As you can see above both, actual and planning figures are stored in the field “Sales”.

Now just create your chart as usually, using “Customer” as dimension and “sum(Sales)” as expression.

And now the clue:
Go to “Line Styles” in the properties of your expression and add the following expression:

[qvl]
=if(IsForecast = -1, ‘<S3>’, ‘<S1>’)
[/qvl]

Attribute expressions for setting the line-style

Just a little explanation:

You can define the line style by using the following options:

• <S1> – continous (default)
• <S2> – dashed
• <S3> – dotted
• <S4> – dashed/dotted

Furthermore you could use the tag <Wn>, where n defines the width of the line, e.g. <W2.5>.

Note:
It is really worth having a look at the chapter “Line Style” or in general “Attribute expressions” in the QlikView Reference Manual.

So all columns for example with a prefix “F_” should be deleted after loading all tables from a database.

My first response was, that the “Table functions” (please have a look at the help-files of QlikView) could be used and I answered with the following pseudo-code using the builtin-QV-functions NoOfTables, TableName, NoOfFields, FieldName:

[qvl]
// ******************************************************************
// Note: this is just a pseudo-code …
// Note: this does not work, please finish reading this blog-post to
// the end for getting the correct code
// ******************************************************************
FOR t = 0 TO NoOfTables() -1

LET vTableName = TableName(t);

// Loop through all fields/columns within the table
FOR vColumnCounter = 1 TO NoOfFields(vTableName)

LET vFieldName = FieldName(vColumnCounter, vTableName);

// If the prefix matches the beginning of the fieldname => drop the field
if (left(vFieldName,2) = ‘F_’) then
DROP FIELD $(vFieldName) FROM $(vTableName);
end if

NEXT
NEXT
[/qvl]

During the dog-walk some hours later, I realized that this would not work in some/most cases (if we have more than one field/column matching our prefix per table):

• After deleting a column, the index of all columns with a higher index than the deleted one would change (decrease by one)
• So maybe we would not fetch all appropriate fields/columns

But how can we change the script to get it to work?

The answer is quite easy:

• We have to loop through all tables and fields/columns first (as demonstrated in the pseudo-code above)
• While looping we would have to save the information which columns should be dropped and
• Finally we would drop the “saved” columns

The first approach which came in my mind was just to save the information which tables should be dropped in a temporary-table, and this works nice:

[qvl]
// ******************************************************************
// Procedure for deleting all fields matching a given prefix.
// ~~
// Usage:
// Call DeleteFieldsByPrefix(‘F_’) would delete all fields starting
// with "F_" within the whole QlikView-application
//
// Further information:
// http://www.qlikblog.at/?p=662
// ******************************************************************
Sub DeleteFieldsByPrefix(prefix)

// Loop through all tables
FOR vTableCounter = 0 TO NoOfTables() -1

// fetch the tableName
LET vTableName = TableName(vTableCounter);

// Loop through all fields/columns within the table
LET vNumOfFields = NoOfFields(vTableName);
FOR vColumnCounter = 1 TO vNumOfFields

LET vFieldName = FieldName(vColumnCounter, vTableName);

// If the prefix matches the beginning of the fieldName
// we "save" the TableName and FieldName for later
// dropping …
if (left(vFieldName,len(prefix)) = prefix) then
Temp_TableToDrop:
LOAD
‘$(vTableName)’ as TableName,
‘$(vFieldName)’ as FieldName
AUTOGENERATE (1);
end if

NEXT // fields looping

NEXT // table looping

FOR i = 0 TO NoOfRows(‘Temp_TableToDrop’)-1

LET vTableToDropFrom = peek(‘TableName’,$(i),’Temp_TableToDrop’);
LET vFieldToDrop = peek(‘FieldName’,$(i),’Temp_TableToDrop’);

TRACE Dropping field $(vFieldToDrop) from $(vTableToDropFrom) …;
DROP FIELD [$(vFieldToDrop)] FROM [$(vTableToDropFrom)];

NEXT

// Finally delete the temporary table
DROP TABLE Temp_TableToDrop;

// Reset/drop variables
LET i = null();
LET vTableName = null();
LET vTableCounter = null();
LET vColumnCounter = null();
LET vNumOfFields = null();
LET vFieldName = null();
LET vTableToDropFrom = null();
LET vFieldToDrop = null();

End Sub
// __________________________________________________________________
[/qvl]

For starting/stopping/restarting the QlikView Windows-services of QlikView server in version 9 I have prepared three batch-scripts which I call on demand:

Restarting all QlikView related Windows-services

@echo off
REM -------------------------------------------------------
REM - File: QlikViewServer9_Restart.bat
REM - Description: Restart's QlikView Services (v9)
REM - Author: Stefan WALTHER
REM -------------------------------------------------------
echo Restarting QlikView Services
echo ======================================================

net stop "QlikView WebServer"
net stop "QlikViewServer"
net stop "QlikView Publisher Command Center Service"
net stop "Qlikview Management Service"
net stop "QlikView Distribution Service"
net stop "QlikView Directory Service Connector"

net start "QlikView WebServer"
net start "QlikViewServer"
net start "QlikView Publisher Command Center Service"
net start "Qlikview Management Service"
net start "QlikView Distribution Service"
net start "QlikView Directory Service Connector"

echo ======================================================
echo QlikView restarted

REM pause

(Just) stopping the QV Windows-services:

@echo off
REM -------------------------------------------------------
REM - File: QlikViewServer9_Stop.bat
REM - Description: Stop all QlikView related services (v9)
REM - Author: Stefan WALTHER
REM -------------------------------------------------------
echo Stop QlikView Services
echo ======================================================

net stop "QlikView WebServer"
net stop "QlikViewServer"
net stop "QlikView Publisher Command Center Service"
net stop "Qlikview Management Service"
net stop "QlikView Distribution Service"
net stop "QlikView Directory Service Connector"

echo ======================================================
echo All QlikView related services have been stopped ...

REM pause

(Just) starting the QV Windows-services:

@echo off
REM -------------------------------------------------------
REM - File: QlikViewServer9_Start.bat
REM - Description: Starts all QlikView related services (v9)
REM - Author: Stefan WALTHER
REM -------------------------------------------------------
echo Beginning to start QlikView Services
echo ======================================================

net start "QlikView WebServer"
net start "QlikViewServer"
net start "QlikView Publisher Command Center Service"
net start "Qlikview Management Service"
net start "QlikView Distribution Service"
net start "QlikView Directory Service Connector"

echo ======================================================
echo All QlikView related services have been started ...

REM pause

Gartners Magic Quadrant 2009

Gartners Magic Quadrant 2010

The most interesting fact in Gartner’s analysis is that QlikTech/QlikView is not a visionary anymore!
Some quotes from Gilles article:

THERE ARE NO VISIONAIRIES ANYMORE!! Even Qlikview isn’t a visionary anymore.

… the explanation for Qlikview not being part of the visionaries anymore is quite understandable. Other parties are copying the unique selling points of Qlikview. Microsoft introduces PowerPivot, SAP created an easy entry proposition with Business Objects Explorer, and Cognos came with Express, all focusing at business users, some of them with in-memory techniques, enabling business users to what Gartner calls “Surf and Safe”. That “proves” that Qlikview is on the right track with the big vendors copying Qlikview’s approach.

One major issue that Gartner is pointing out in its analysis is that Qlikview could have had its momentum. Qlikview is/has been very successful with in-memory technology and 64-bit computing enabling scalability of Qlikviews model.

To summarize what Gartner says about Qlikview’s strengths, we can be very short: It is easier, simpler, cheaper, faster, quicker to deploy and is more feature rich than the competition.

To conclude this post: Qlikview is still going strong and has a very good product (and marketing), but Qlikview needs to show some vision on the short and medium term. Vision is not about incrementally adding new functionality, but more about how to service those large enterprise deployments. Qlikview has to do some serious work on an enterprise wide semantic data layer and better tools to manage large deployments.

I was asked how one could run QlikView 8.5 and QlikView 9 one one system …

That’s absolutely no problem.

During the installation of QlikView 9 Desktop (when having QlikView 8.5 already installed) just choose another installation path as suggested (e.g. C:\Program Files\QlikView_9 instead of C:\Program Files\QlikView) and these two versions will run on one system without conflicting each other.

That’s all …

Note: This works for me on Windows 20003 Server, Windows XP, Vista and Windows 7

Sure, if you are looking into the reference-manual or into the help file, you’ll find the SWITCH-CASE-statement, but this is a control statement so it cannot be used within a load-statement, e.g. this is possible

[qvl]
switch I
case 1
load ‘$(I): CASE 1’ as case autogenerate 1;
case 2
load ‘$(I): CASE 2’ as case autogenerate 1;
default
load ‘$(I): DEFAULT’ as case autogenerate 1;
end switch
[/qvl]

But this not:

[qvl highlight=”1″]
// Note: this pseudo-code will not work!!!
LOAD
Profession,
(SELECT CASE Profession
CASE ‘Profession A’: 100
CASE ‘Profession B’: 200
CASE ‘Profession C’: 300
CASE ‘Profession D’: 400
DEFAULT: 1000
END SELECT) as RISK_CLASSIFICATION
RESIDENT FirstTable;
[/qvl]

Even if I do not really know why QlikTech has not implemented this, we do not really need it.

Instead of using a SELECT-CASE (SWITCH-CASE) functionality in load-scripts we can easily use ApplyMap method:

Let’s think about the following scenario:

• We have a field called “PROFESSION”
• Depending on the values in this field we want to create a field “RISK_CLASSIFICATION”

Let’s create a sample for loading the field “PROFESSION”, would normally be loaded from your database or other data-sources:

[qvl]
FirstTable:
LOAD * INLINE [
Profession
Profession A
Profession B
Profession C
Profession D
];
[/qvl]

In the next step we create a mapping-table and use the applymap:

[qvl]
Map_Classification:
MAPPING
LOAD * INLINE [
Profession, Classification
Profession A, 100
Profession B, 200
Profession C, 300
Profession D, 400
];

Qualify *;
SecondTable:
Load
Profession,
// Use the applymap to classify the profession, 1000 is the default-value
// if the applymap does not find a match
applymap(‘Map_Classification’,Profession,1000) as RISK_CLASSIFICATION
RESIDENT FirstTable;
[/qvl]

This will result into:

But what, If you do not want that the end-user has to option to select the desired macro security/module security?
You can (e.g. as a system-administrator) globally enable the module-security at the highest level (“Allow any Macro (only for trusted documents)”) for every QlikView-Server you have by running the following script.
This script adds a registry entry to HKCU\Software\QlikTech\QlikOcx\Settings for Qlikview Servers\:

'// **************************************************
'// Script for adding some registry keys to the current user profile/registry settings
'// for enabling the macro security/module security for the SERVER defined below
'// ~
'// CONFIGURATION
'// Just configure the script by defining your server below
'// ~
'// The article explaining this script can be found at 
'// http://www.qlikblog.at/523/
'// **************************************************
CONST cSERVER_NAME = "YOUR_SERVER_NAME"
Dim WshShell 'as Object

Set WshShell = WScript.CreateObject("WScript.Shell") 
WshShell.RegWrite "HKCU\Software\QlikTech\QlikOcx\Settings for Qlikview Servers\", 1, "REG_SZ" 
WshShell.RegWrite "HKCU\Software\QlikTech\QlikOcx\Settings for Qlikview Servers\Module Script System\", 1, "REG_SZ" 
WshShell.RegWrite "HKCU\Software\QlikTech\QlikOcx\Settings for Qlikview Servers\Module Script System\ " & cSERVER_NAME, "", "REG_SZ" 

That’s it!
By doing so the end-user will never be asked again to choose the desired macro-security/module security.
You could for example run this script together with other logon scripts, it does not matter if you run this script multiple times!

This behavior is quite annoying …!

But the workaround explained here will show you a possibility how you can “simulate” passing parameters to macro-functions:

The idea behind is quite simple. In QlikView 9 we have now actions which can be added for any event (e.g. the OnClick event for buttons). The clue is that we can add multiple actions for every event, so I am doing the following:

OnClick Event for Button “cmdXY”:

• Changing the value of a variable (e.g. vValueToDisplay)
• Calling a function/sub in your macro (e.g. cmdXY_OnClick)
• Within the macro and the function cmdXY_OnClick I am first retrieving the value for the variable vValueToDisplay, then I am executing the “normal” macro-code using the value read from the variable

Step by step with some screenshots:

First let’s create the variable vValueToDisplay:

Then let’s create some buttons:

Now we have to assign the actions for “Button 1”:

Explanation:

• Go to the properties of the button
• Change to the Tab Actions
• Then select Action-Type External and Set Variable

Then we set the desired parameters:

Then we have to trigger the macro:

• Add a second action of Action-Type External and Run Macro and enter the following values:

So, we have nearly finished, the last step is to create the macro (Go to Tools => Edit Module or use Shortcut Ctrl+M):

'// *************************************************************
'// Displays a simple message-box, showing the value of the
'// variable vValueToDisplay
'// ~~
'// This sub assumes that the variable "vValueToDisplay"
'// has been set before !!!
'// *************************************************************
sub GenericClick

'// First let's retrieve the content of the variable "vValueToDisplay"
Dim strValueToDisplay 'as String
strValueToDisplay = ActiveDocument.Variables("vValueToDisplay").GetContent().String

'// Create an alert
msgbox("The value of ""vValueToDisplay"" is: " & strValueToDisplay)

end sub

Clicking on the button will now show the content of the variable:

Further examples:

Please find some further usage-examples in the following QlikView-example-application:

How I am using this:

The example above is quite simple. In reality I am using for more complex situations, e.g. for creating Excel-sheets and so on. Therefore I am setting some variables before and then I am calling the desired function which requires the above variables to be set before …

Note at the end:

In my example you’ll find a sheet “Testing Real Parameters” where I have tested passing “real” parameters to macro-functions, but did not succeed.
Maybe I just did not manage to call a macro with a parameter correctly … If so, please tell me!

(The original German article can be found at http://www.qlikblog.at/9/tip-statusboxauswahlstatus-in-excel-export-integrieren/)

The functionality in QlikView that any object-content can be easily exported to Excel is very practical and often used.
But now it is also a common requirement that the generated Excel sheet should also contain some information which selections have been made in QlikView, one just need to know on which data the results in Excel are based on …
Good news! This feature is available in QlikView, although it is not enabled by default:

• Go into the user settings (menu: Settings” => “User Preferences” or Ctrl+Alt+U) and then on the “Export“)
• Now you have to check the option “BIFF Export” in the section “Selection Stamps in Export”

User Settings in QlikView Windows Client for enabling transferring the "Current Selections" to Excel

After having set these options the “Current Selection” will be exported at the end of your Excel-file:

Exporting an object in QlikView to Excel

Current selections of QlikView shown in Excel

Does this also work on the QlikView Server?

So far so good, but what happens when you open the document using the IE plugin via the Access Point on the QlikView Server?

Of course, nothing at all, it is a user setting, so this setting, we have set for the Windows client of QlikView is not available for the IE-Plugin.

A quick glance into the user settings in IE plug shows that this assumption is correct and that this option can be set on the server as well:

User preferences in the QlikView IE-Plugin

Under Menu => User Preferences (or Ctrl+Alt+U) and “Export” we find again the familiar dialogue, the “BIFF Export” is also disabled by default:

User settings in QlikView IE-Plugin

Now you can, of course, can waive the end user to select this option or not, if this is not desired, you can explicitely set this option by setting a special registry key to the user.
This registry-key called “[HKEY_CURRENT_USER\Software\QlikTech\QlikOcx\Settings 7]

The attached registry-file could for example be delivered with other settings during automated installation of QlikView or other user related startup-tasks within your environment:

[HKEY_CURRENT_USER \ Software \ QlikTech \ QlikOcx \ Settings 7]
"SelectionStampInBIFFExport" = "1"

Doing so it is easy to encapsulate and reuse some code used in several QlikView applications.

So organizing your code in load-scripts is easy, you can use several tabs and the $(include) command, whereas QlikView is not offering similar possibilities in the macro-editor. There are no tabs and “officially” there does not exist an corresponding command for including code in macros.

But that’s not the whole story, there is a nice possibility:
QlikTech is using the VBScript engine for interpreting the macro code by default (you could also use the JavaScript engine …). In VBScript there is a not very well known command called “ExecuteGlobal” (Official reference at MSDN, which “executes one or more specified statements in the global namespace of a script.”

So can we use that in QlikView macros?

Yes, we can!

Dim objFSO 'as Object
Dim objFile 'as Object
Dim strScript 'as  String

'// Open the File using File-System-Objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("SOME_SCRIPT_TO_ENCLUDE.vbs")

'// Copy the content of the file
strScript = file.ReadAll

'// Just destroy the objects again
Call objFile.Close()

'// Execute the script globally
Call ExecuteGlobal(strScript)

Doing so at the beginning of the script you can now use the content defined in SOME_SCRIPT_TO_ENCLUDE.vbs in the subsequent macro code.

Because I am using this possibility of including existing scripts quite often I have improved the code above a little bit and created a function which I use in every application where I need this stuff:

'// *****************************************************************
'// Function for executing external content in your application.
'// ~
'// Parameters:
'//	p_strFilePath – full absolute path in relation to the QlikView application
'// ~
'// Return Value:
'//	Will return true if succeeded, otherwise false (e.g. if the file 
'//	does not exist)
'// Examples:
'//	IncludeAndExecute(“D:\QlikView\Scripts\FileFunctions.vbs”) 
'// Further information: http://www.qlikblog.at/509/
'// ******************************************************
Private Function IncludeAndExecute(ByVal p_strFilePath) 'as Boolean

'// Variable declaration
Dim objFSO 'as Object
Dim objFile 'as Object
Dim strScript 'as  String

'// Open the File using File-System-Objects
Set objFSO = CreateObject("Scripting.FileSystemObject")

'// prevent errors ...
On Error Resume Next

	Set objFile = objFSO.OpenTextFile(p_strFilePath)
	
	'// Copy the content of the file
	strScript = objFile.ReadAll

	'// Just destroy the objects again
	Call objFile.Close()

	'// Execute the script globally
	Call ExecuteGlobal(strScript)

	'// If an error occurred just return false
	If (len(Err.Description) > 0) Then
		IncludeAndExecute = false
		
		'// Just comment the following line if you want to suppress messages 
		'// in case of errors
		msgbox(Err.Description)
		
		Exit Function
	End If
On Error Goto 0

IncludeAndExecute = true
End Function

And then I am using this code as follows:

For testing purposes you can download all the scripts and a sample application by clicking on the link below:

Advantages

• Code reuse and code encapsulation is easier
• Because you are now only using text-files integration into CVS-systems will be easier!
• Because of using the File-System-Objects (FSO) you have to enable “System Access” in the macro-security settings!!!

Disadvantages

• You are creating an unnecessary, artificial dependency between the included scripts and your QlikView-applications! So certainly you have to take care when changing your global scripts …

In the last recent months I had a lot of projects where a tenfold nesting of if-statements was not uncommon.

After struggling with these nested ifs for a while I decided to create a little user-interface which helped me to decrease the time for creating nested if-statements.
Even if this tools is just a little, little helper it may also be helpful for you, so I decided to publish it here:

Try the “Nested If Generator” now

Some screenshots and explanation:

At the beginning you can define the “fieldname” to be generated” and define your first if-statement:
Let’s assume you are checking the two fields “Age” and “Profession”:

Defining the first if-statement

If you want to add an additional if-clause just click on “Add another IF-block”:

Adding a second if-statement

As you can see when comparing the two screenshots above the “else-block” of the first statement was removed and replaced by an additional if-clause.

After some further if-blocks the configuration could look like as follows:

After adding several if-statements

Now the last step is easy, change to the “Generated Code” tab and copy your code:

Generated Code

The Tool is offering three different “Formatting Styles”:

Single line formatting

Single line formatting

If-statements formatted like this were the main-reason for creating this tool

Indent style with one line per condition

Indent style with one line per condition

This is my preferred formatting-style for really large if-statements with complex conditions

Indent style with one line per if-statement

Indent style with one line per if-statement

This is my preferred formatting-style for if-statements with short conditions like demonstrated in this example …

Have fun

But most of the mails I received were targeting one topic: “Why is your blog published in German” or “Could you please translate the article XY for me into English”, and so on …

Originally I have decided to publish this QlikView blog in my mother tongue, mainly because this blog was primarily planned as a service for my existing (and new customers) and they are located in Austria, Germany and Switzerland.

But I am indeed very interested in getting as much feedback as possible from the whole QlikView-community, that’s the main reason why qlikblog.at will from now on be published in English only.

So stay tuned, what can you expect from qlikblog.at in 2010:

• In the last two months I have developed about a dozen of tools which should help you to develop your QlikView projects
• In the next weeks I will also try to translate most of the existing articles written in German to English, so do not hesitate to revisit the page …
• Certainly also new topics will be covered here …

So, do not miss any new article here and subscribe to the RSS-feeds or subscribe to the newsletter (which will only be sent to you when new articles are published).

Looking forward to an interesting year 2010.
Best regards
Stefan

Dazu wenden Sie bitte im QlikView-Windows-Client folgenden Befehl an:

Menü: “Ansicht” => “Zoom auf Bildschirmgröße anpassen”

Dadurch passt sich der Zoom des aktuellen Arbeitsblattes auf die aktuelle Bildschirmgröße an und zwar so, daß alle Objekte dieses Arbeitsblatts ohne zu Scrollen im Fenster sichtbar sind.

Wenn Sie diesen Zoom anschließend auf alle weitere Arbeitsblätter / Sheets übertragen wollen, dann gibt es auch eine Möglichkeit dazu:

Menü: “Ansicht“ => “Zoom auf alle Arbeitsblätter übertragen”

Zoom mit Makros automatisieren

Die oben beschriebene Funktionalität läßt sich nun auch mittels Makro automatisieren. Dieses Makro könnte zB im DocumentOnOpen-Event, oder dergleichen eingebunden werden:

ActiveDocument.ActiveSheet.FitZoomToWindow 
ActiveDocument.GetApplication.WaitForIdle 
ActiveDocument.ActiveSheet.ApplyZoomToAllSheets
ActiveDocument.GetApplication.WaitForIdle 

Dann wird ein Raster dargestellt, der einem hilft, Objekte anzuordnen:

Standardraster im Entwurfsmodus

Mir persönlich ist dieser Raster so grob zum Arbeiten, lieber hätte ich den feinmaschiger:

Benutzerdefinierte Rastergröße im Entwurfsmodus

Diese Einstellung finden Sie unter:
Menü: „Einstellungen“ -> „Benutzereinstellungen“ (bzw. Strg/Ctrl + Alt + U), dann im Reiter „Design“ den „Linienabstand (mm)“ auf die gewünschte Größe einstellen (in meinem Fall präferiere ich 5 mm).

Einstellen des Layoutrasters in den Benutzereinstellungen

Wenn der Eigenschaftsdialog im QlikView-Windows-Client plötzlich so aussieht:

Fehlende Layoutoptionen im Eigenschaftsdialog von Objekten

Sie hätten diesen Dialog aber gerne (wieder) so:

Eigenschaftsdialog eines Objektes mit erweiterten Layoutoptionen

Dann ist nur folgendes zu tun:

Menü: „Einstellungen“ -> „Eigenschaften des Dokumentes“ (bzw. Strg/Ctrl + Alt + D) und dann im Reiter „Allgemein“ das „Aussehen der Objekte“ auf „Erweitert“ einstellen:

Einstellen der erweiterten Layoutoptionen in den Benutzereinstellungen

Wann genau diese Option sich zurückgestellt hat, konnte ich bei mir nicht reproduzieren; zunächst dachte ich, es hängt mit der Installation von QlikView 9 zusammen; da sich dieses Verhalten auch bei Kunden mit QlikView 8.5 gezeigt hat, kann ich keine schlüssige Aussage mehr dazu treffen

Was tun, wenn Sie die Daten dennoch „außer Haus“ geben wollen und müssen, z.B. wenn Sie auf Ihrem Laptop weiterentwickeln wollen, wenn Sie eine Beispielapplikation an den Support oder an eine QlikView-Berater schicken wollen?
Kein Problem, dafür gibt es eine Funktionalität in QlikView, die diese Anforderungen abdeckt: Verschlüsselung!

Spielen wir die Verschlüsselung anhand einer einfachen Beispielapplikation durch, die folgende Daten beinhält:

Beispiel der Verschlüsselung, Schritt für Schritt

Datenstruktur der Demo-Applikation

Wenn die Daten sorglos aus der Hand gegeben werden können sollen, dann wären folgende Felder zu verschlüsseln:

Tabelle Kunden:

• Firma
• KontaktName
• Straße
• Telefon

Tabelle Bestellungen:

• Spediteur
• UmsatzBestellung

Also, machen wir uns ans Werk:
Unter Einstellungen -> Eigenschaften des Dokumentes (bzw. Strg + Alt + D) gibt es den Reiter Verschlüsselung:

Viel brauche ich jetzt nicht mehr zu erklären:

• Wählen Sie die gewünschten Felder im linken Bereich aus
• und klicken Sie anschliessend auf den „Verschlüsseln“ Button (mit gedrückter Strg/Ctrl-Taste können mehrere Felder selektiert werden)
• Je nachdem, wie groß Ihre QlikView-Applikation ist, kann der Vorgang des Verschlüsselns natürlich einige Zeit in Anspruch nehmen

Achtung: Beachten Sie bitte, dass der Vorgang des Verschlüsselns nicht mehr rückgängig zu machen ist, daher vorher eine Sicherung des Originaldokumentes machen!

Wie werden die Daten verschlüsselt?

Schauen wir uns abschließend noch schnell an, was aus den Originaldaten geworden ist:

Nachfolgende Grafik zeigt jeweils die Daten der Felder „UmsatzBestellung“ und „Firma“ vor und nach der Verschlüsselung an.

Gegenüberstellung der Daten vor und nach der Verschlüsselung

Auffallend ist (zum Glück), dass die Verschlüsselung intelligent umgesetzt wurde:

• Aus Text bleibt Text (da werden einfach nur Buchstaben vertauscht)
• Zahlen bleiben Zahlen, auch das Format bleibt gleich, selbst die Größenordnung der Zahlen bleibt ungefähr in einer sinnvollen Relation.

So lassen sich mit diesen Daten auch sinnvolle Demos entwickeln!

[qvl]
// Store table Customers into Qvd-file
STORE Customers INTO Customers.qvd (qvd);
[/qvl]

Dabei ist (auch in vorherigen Versionen) der Parameter nach dem Dateinamen, als “(qvd)” optional, wenn man diesen weglässt, dann funktioniert das Speichern in QVD-Dateien trotzdem.

Wozu dieser zusätzliche Parameter (auch schon in vorhergehenden Versionen) gedacht war, erkennt man erst mit QlikView 9, denn nun lässt sich auch folgender Befehl ausführen:

[qvl]
STORE Customers INTO Customers.csv (txt);
[/qvl]

Mit „(txt)“ am Ende des STORE INTO – Befehls wird die Tabelle in ein Text-File im CSV-Format exportiert . Als Trennzeichen (Delimiter) für das CSV-File wird ein Beistrich „,“ verwendet; wie andere Trennzeichen verwendet werden können, scheint noch nicht dokumentiert zu sein, zumindest finde ich es nicht:

[qvl highlight=”12″]
Customers:
LOAD * INLINE [
Country, Customer, CustomerId
Austria, Customer A, 1
Germany, Customer B, 2
Switzerland, Customer C, 3
Denmark, Customer C, 4
];
STORE Customers INTO Customers.qvd (qvd);

// Store table countries into .csv-file
STORE Customers INTO Customers.csv (txt);

[/qvl]

Dieses Beispiel wird daher folgenden Inhalt in der Datei Customers.csv aus:

[qvl]
Country,Customer,CustomerId
Austria,Customer A,1
Germany,Customer B,2
Switzerland,Customer C,3
Denmark,Customer C,4
[/qvl]

Definition des Trennzeichens / Delimiters

Das Trennzeichen, welches standardmässig verwendet wird, ist ein Beistrich, also “,”, das Trennzeichen (der Delimiter) kann jedoch verändert werden.
Obwohl nicht offiziell genau so dokumentiert, funktioniert das Definieren des Delimiters für den STORE INTO-Befehl wie folgt:

[qvl]
// change the delimiter to a pipe “|”
STORE Customers INTO Customers.csv (txt, delimiter is ‘|’);

// or change the delimiter to “;”
STORE Customers INTO Customrs.csv (txt, delimiter is ‘;’);
[/qvl]

Fazit:

• Eine praktische Erweiterung in QlikView, die vor allem QlikView offener in der Zusammenarbeit mit anderen System macht.
• Warum mit dieser neuen Funktionalität nicht auch gleich die Möglichkeit implementiert wurde, Tabellen in XML-Dateien zu exportieren, bleibt mir ein Rätsel; es ist aber anzunehmen, dass „STORE Customers INTO Customers.xml (xml)“ in einer der nächsten Versionen möglich sein wird Beim Schreiben dieses Artikels bin ich draufgekommen, dass “STORE Customers INTO Customers.xml (xml)” schon funktioniert, jedoch nicht offiziell dokumentiert ist, darüber aber bald mehr … die Funktionalität “STORE Customers INTO Customers.xml (xml)” macht nichts anderes als eine QVD zu generieren …

QlikTech CEO Lars Bjork

Die Veröffentlichung dieses Artikels ist zwar schon eine Weile her, zur Sicherheit poste ich es aber nochmals für jene, die diesen Artikel noch nicht kennen:

Kurzauszug einiger Aussagen:

“Your brain is associative,” says Lars Bjork, CEO of QlikTech.

Making search much more like your brain — and applying it to business analysis — is what has transformed QlikTech into one of the hotter business-intelligence-software companies around.

The QlikView software lets users decide what data they want to collect, rather than sort through an information hierarchy.

Link zum Artikel: http://www.time.com/time/magazine/article/0,9171,1912425-1,00.html

[Section1]
Name1=Value1
Name2=ValueXX
Name3=ValueYY
[Section2]
Name4=Value3

Nachfolgender Code ladet eine beliebige Ini-Datei und bringt diese in strukturierte Form:

[qvl]
// **************************************************************
// this assumes that the ini-file defined below is located in the
// same folder as the qvw-file.
// **************************************************************
SET vIniFilePath = ‘Test4.ini’;

// **************************************************************
// Create a temporary table
// Note when reading the data-transformation:
// You have to read the load-statements bottom-up !!!
// **************************************************************
Temp_Ini:
Load
// get the name
if(_FlagSection = 0,left(FullField, index(FullField,’=’)-1)) as Name,

// get the value
if(_FlagSection = 0,mid(FullField, index(FullField,’=’)+1)) as Value,
*
;
Load
// just again a load to fill up the blank rows with the corresponding section
if(_FlagSection = 0, text(peek(‘Section’,RecNo()-2)),Section) as Section,
FullField,
Id,
_FlagSection
;
Load
// if we are dealing with a section (see below) we cut the string inside
// the ‘[‘ and ‘]’ characters
if (left(FullField,1) = ‘[‘ and right(FullField,1) = ‘]’,purgechar((FullField),'[]’)) as Section,

// if we have a ‘[‘ character at the beginning and a ‘]’ character at the end of
// the string we are dealing with a section, so flag the record as section
if (left(FullField,1) = ‘[‘ and right(FullField,1) = ‘]’,1,0) as _FlagSection,
*;
LOAD
RecNo() as Id,
@1 as FullField
FROM $(vIniFilePath)
(txt, codepage is 1252, no labels, delimiter is ‘,’, msq);

// **************************************************************
// Load the values again to ‘drop’ the rows with sections in it.
// **************************************************************
Ini:
LOAD
Id as LineNo,
Section,
Name,
Value,
FullField as LineString // just for testing and review,
RESIDENT Temp_Ini
WHERE (_FlagSection = 0)

// prevent comments
and (left(Value,1) <> ‘;’)

// prevent comments
and (left(Name,1) <> ‘;’)

// prevent selecting empty-lines
and ((len(Name) > 0) and (len(Value) > 0))
Order BY Id
;

//drop the temporary table which is not used anymore
Drop TABLE Temp_Ini;
[/qvl]

Damit lassen sich die Daten in strukturierter Form an der Oberfläche anzeigen:

Anzeige der Daten der Ini-Datei

Verbesserungen:

Dinge, die ich in meinem Script nicht beachtet habe:

• Escape Character
• Kommentare am Ende einer Zeile
• usw.

Downloads

QlikView-Applikation mit Beispiel-Dateien (.zip, 1.2 MB)

Screenshot des Windows-Explorer Kontextmenüs auf meinem Rechner

„QlikView Archive Document“? … Nie davon gehört, also begebe ich mich auf die Suche, doch leider erfolglos. In keiner Dokumentation von QlikView (weder Client, Server, API, etc.) auch nur der kleinste Hinweis auf diese Art von Dokumenten, die die Endung .qar haben … Auch ein demensprechender Post auf community.qlikview.com bringt nicht viel Neues, ausser das ich nicht der einzige bin, der diese Entdeckung gemacht hat …

Spannend, wir werden sehen, was es für Neuheiten auf dieser Front gibt … Ich werde sicher darüber berichten.

• Variante 1: erzwungender Zeilenumbruch mit “\n”
• Variante 2: erzwungener Zeilenumbruch mit Chr(13) und Chr(10) (carriage return und line feed)

In einer Formel sieht das dann so aus:

Variante 1:
[qvl]
=’This is a long text, this is the first line\nand this is the second line’
[/qvl]

Variante 2:
[qvl]
=’This is a long text, this is the first line’ & Chr(13) & Chr(10) & ‘and this is the second line’
[/qvl]

Dies führt dann zu folgendem Ergebnis:

Erzwungene Zeilenumbrüche mit Variante 1 & Variante 2

• Löschen über die Benutzeroberfläche/den Windows Client
• Löschen im Load-Script
• Löschen mit einem Makro 

Variante 1: Löschen über den Windows-Client

Das Löschen über die Benutzeroberfläche erfolgt über folgende Schritte

Menü -> Einstellungen -> Variablenliste (oder gleich Ctrl/Strg + Alt + V)

dann im Dialog eine Variable auswählen und auf „Löschen“ klicken.

Löschen einer Variable über die Benutzeroberfläche

Soweit so klar und sicher nicht neu für Sie, ist das Löschen jedoch auch im Load-Script möglich?

Variante 2: Löchen von Variablen im Load-Script

Variablen können im Load-Script gelöscht werden mit

[qvl]
// the name of the variable is vVariable

// first possibility for deleting variables within the load-script
SET vVariable = ;

//second possibility for deleting variables within the load-script
LET vVariable = null();

[/qvl]

So klingt zwar einfach, funktioniert jedoch nur unter folgenden Bedingungen:

• Die Variable, die während des Load-Script erstellt wurde bzw. mit der gearbeitet wurde, darf NIE am Ende des Scripts vorhanden sein.
• Sobald die Variable einmal nach Ende des Scriptlaufs vorhanden ist, kann diese nur mehr über die Oberfläche gelöscht werden (Variante 1)

Variante 3: Löschen über ein Makro

Zu guter letzt bleibt noch zu erwähnen, dass man eine Variable auch mittels Makro löschen kann:

Makro-Code (VBScript):

' the only parameter for the function "RemoveVariable" is the name of the variable
' in this case "vTestVariable"

ActiveDocument.RemoveVariable "vTestVariable1"

Hinweis: Es ist nicht notwendig, zu überprüfen, ob die Variable existiert oder nicht; wird ActiveDocument.RemoveVariable für eine nicht existierende Variable aufgerufen, kommt es zu keinem Fehler.

For the translated version of this article please click here.

In der Ansicht der Tabellenstruktur (Ctrl + T bzw. Datei => Tabellenstruktur) kann man nun im Kontextmenü eine Vorschau des Tabelleninhaltes anzeigen lassen:

Durch Rechtsklick auf die Tabelle kann man die Vorschau öffnen

Angezeigt wird dann ein Grid mit der Vorschau der Daten:

Vorschau der Daten einer in QlikView geladenen Tabelle

Interessanteste Aussagen in diesem Artikel:

• “This leads Deighton to a key difference between QlikView and Cognos Express. Whereas QlikView is based on an associative database that provides multidimensional analysis capabilities, Cognos Express is rightly described as a full OLAP product, which creates and stores “cubes” of multi-dimensional data.”
• “At the end of the day, Deighton (senior vice president of QlikTech) welcomes the competition from IBM, and the renewed focus it brings on developing mid-market BI software. “There tends to be a lower adoption of traditional BI technology in the mid market. It’s expensive and difficult to use,” he says. “The more we can get people to focus on this part of the industry, the better.””

Zum Artikel “QlikTech Welcomes Competition with IBM Cognos Express”

Suchbox

Mit der Suchbox wurde ein neues Objekt eingeführt, mit dem man den Benutzer in mehreren Felder suchen lassen kann. Statt vieler Listboxen kann daher ein Objekt zur Verfügung stellen, welches die Suchmöglichkeit über (beliebig) viele Felder in der QlikView-Applikation zulässt:

Screenshot einer Suchbox für mehrere Felder in QlikView 9

Mehr dazu unter “QlikView 9 – Neue Objekte: Suchbox”

Hierarchische Listbox

Die hierarchische Listbox ist nichts anderes als ein bereits aus anderen Applikationen bekannter Treeview. Darin lassen sich hierarchische Abhängigkeiten von Feldern abbilden, z.B. Organisationseinheiten, Produktgruppen- und Untergruppen, Regionen- und Länder, etc.):

Hierarchische Listbox in QlikView 9

Document Chaining

In QlikView 9 ist es mithilfe von „Document Chaining“ nun möglich, mehrere Dokumente miteinander zu verknüpfen und die in einem Dokument durchgeführten Selektionen von einem Dokument in das neu geöffnete zu übertragen.

Typischer Anwendungsfall:

• Sie haben eine „Haupt-Analyse-Applikation“ mit den wichtigsten Dimensionen in denen die Benutzer Ihre Analysen durchführen.
• Basierend auf diesem QlikView-Dokument kann der Benutzer weiterführende Detail-Applikationen öffnen, wobei beim Öffnen alle getroffenen Selektionen der Haupt-Applikationo in der „Satelit-Applikation“ übertragen werden.

Button „Auswahl aufheben“

Die Funktionalität des Buttons „Auswahl aufheben“ wurde dahingehend erweitert, als dass der Benutzer die Möglichkeit hat, eine beliebige Selektion als „persönlichen“ Ausgangsstatus zu definieren, zu dem er jederzeit wieder zurückkehren kann.

Mehr dazu unter: „QlikView 9 – ‚Auswahl aufheben‘ wird zu ‚Auswahlstatus‘“

Hyperlinks in Tabellendiagrammen

In Tabellendiagrammen kann Spalten so definieren, dass diese als Hyperlink dargestellt werden.

Tabellendiagramm mit einer Spalte mit Hyperlinks

Mehr dazu unter: „QlikView 9 – Hyperlinks in Tabellendiagrammen“

Mini Charts

Ab QlikView 9 lassen sich sogenannte „Mini-Diagramme“ innerhalb von Tabellendiagrammen darstellen:

MiniCharts in einem Tabellendiagramm

Trellis Charts / Gitterdiagramme

Mithilfe von sogenannten „Trellis Charts“ lassen sich mehrere Charts pro Dimension in einem Objekt anzeigen:

Beispiel eines Trellis-Chart / Gitterdiagramm

Whisker Charts

Mit den Whisker Charts wurde ein Chart-Typ eingeführt, mit dem sich Börsenkurse und dergleichen darstellen lassen. Das Hauptcharakteristikum dieses Chart-Typs ist es, dass jeder Datenpunkt vier Werte beinhaltet: Minimum, Maximum, Open und Close:

Beispiel eines Whisker-Charts in QlikView 9

Dabei ist folgendes aufgefallen (wenn ich nichts übersehen habe):

• Am Windows-Clients wurden hauptsächlich Bugs ausgebessert.
• Am Publisher/Server wurden Teile der Oberfläche überhaupt überarbeitet, teilweise sogar redesigned (leider habe ich nicht mehr beide Versionen parallel auf die Schnelle zur Verfügung, sonst könnte ich hier ein paar “überarbeitete” Screens präsentieren.
• Der Publisher/Server, der im “Initial Release” wirklich noch die eine oder andere gröbere Macke hatte, rennt nun sehr stabil.
• Zu den Mobile-Clients kann ich leider noch nicht sehr viel sagen, da ich diese noch nicht ausführlich getestet habe …

Eine Übersicht der Änderungen findet man im Download-Bereich von QlikView in zwei Quellen:

• Releasenotes zu QlikView 9/SR1 als PDF-Dokument (QlikView-9.00-SR1-build-7257-Release-Notes.pdf)
• QlikView-Applikation mit allen Bugfixes (QlikView 9.00 SR1 build 7257 Fixed bugs.qvw)

Es ist wirklich sehr begrüssenswert, dass die gefixten Bugs nun veröffentlicht wurden und dem Benutzer im Detail zur Verfügung stehen, dies erleichtert die Arbeit ungemein!

Hyperlinks in einem Tabellendiagramm

Dazu stellt man in den Eigenschaften des Diagrammes unter „Formeln“ die Eigenschaft „Repräsentation“ auf „Hyperlink“.

Einstellen der Repräsentation und des Inhaltes für den Hyperlink in den Eigenschaften des Diagrammes

Dann kann den Link unter Definition nach folgender Syntax definieren:
[qvl]=Name & ‘<url>’ & Link[/qvl]
also z.B.
[qvl]’Besuchen Sie qlikblog.at<url>www.qlikglog.at'[/qvl]
in meinem oben dargestellten Beispiel habe ich mit dem Feld „Region“ auf die Google-Search verwiesen:

[qvl]
=’Search in Google<url>http://www.google.de/search?q=’ & Region
[/qvl]

Achtung:
Beim Export nach Excel wird der Link nicht exportiert sondern nur der Text/Name des Links

Fazit:

Eine Mini-Funktionalität mit etwas eigenartiger Syntax, jedoch mit vielen Anwendungsmöglichkeiten im professionellen Einsatz:

• Verlinkung mit Websites
• Verlinkung in das Intranet
• Öffnen von div. Web-Applikationen im Unternehmenseinsatz
• etc.

• Enterprise Manageability—Supports large deployments, data sets, real-time data and PDF reporting
• Cloud Availability—Through Amazon’s Elastic Compute Cloud (EC2) Web service
• QlikView Mobile—Launch of Java Mobile and iPhone clients
• QlikView Personal Edition—Free downloadable developer tool for personal use
• Usability and Simplicity

Nun aber genug von der QlikTech-Marketing-Abteilung, schauen wir uns nun im (grob verkürtzen) Überblick an, was sich in QlikView 9 getan hat.

Personal Free Edition:

Seit der Version 9 von QlikView gibt es nun die QlikView Personal Edition, eine (fast) uneingeschränkte Vollversion des QlikView-Windows-Clients für den privaten Gebrauch.
Mehr dazu unter http://www.qlikblog.at/209/qlikview-9-free-personal-edition/

Server/Publisher:

Server und Publisher, die ja bis inklusive Version 8.5 getrennte Produkte waren, wurden in ein Produkt zusammengelegt und komplett redesigned; da ist kein Stein auf dem anderen gelieben.
Mehr dazu unter http://www.qlikblog.at/241/qlikview-9-ueberblick-ueber-server-und-publisher/

Neue Features des Servers:

Der QlikView Server wartet mit einer Reihe neuer Funktionalitäten auf:

• Neu konzipierter Access-Point mit höherer Bedienerfreundlichkeit
• Neue Funktionalität für „Collaboration“ in den Thin-Clients
• Das Reload-Scheduling für Dokumente kann bereits im Dokument definiert werden
• Support für das SNMP-Protokoll (Simple Network Mangement Protocol)
• Die Beschränkung von 2 Milliarden Datensätzen in einer Tabelle wurde aufgehoben

Real-Time Data Update

Für Echtzeitumgebungen wurde die Funktionalität entwickelt ohne Script-Load die Daten am Server zu aktualisieren. Dafür steht ein API zur Verfügung, mit welchem SQL-ähnliche Befehle (Insert, Update, Delete) für die Manipulation der Speicherdaten abgesetzt werden können.

Layout & User-Interface – Neuerungen

Mit QlikView 9 wurden eine Reihe an verbesserten Layout-Optionen für professionelle Business-Intelligence-Applikationen hinzugefügt:

• Suchbox (siehe dazu “QlikView 9 – Neue Objekte: Suchbox”)
• Hierarchische Listbox (Treeview)
• Mini-Charts
• Trellis Charts
• Sparkline-Charts
• Hyperlinks in Tabellen (siehe dazu “QlikView 9 – Hyperlinks in Tabellendiagrammen“)

Entwicklungsumgebung

Viele der Wizards in der Entwicklungsumgebung wurden verbessert und komplett überarbeitet, Set-Analysis (Auswahlformeln) haben die Erweiterung der „Indirect Set Analysis“ erhalten und die Verwendung von Makros ist nun durch eine neue Funktionalität, den sogenannten Aktionen, oft gar nicht mehr notwendig.

Mobile Clients:

Es gibt nun QlikView für iPhone, der im AppStore erhätlich ist, weiters einen Java Mobile Client, der auch auf Blackberry und Mobilgeräten mit dem Symbian-Betriebssystem funktioniert.

Thin Clients/Ajax/Java:

An den Thin-Clients (Ajax, Java) wurde insofern recht intensiv gearbeitet, als dass diese nun wesentlich mehr Funktionalitäten unterstützen:

• Vorab-Generierung des Ajax-Clients nicht mehr nötig
• Voller Support für Collaboration-Objects im Ajax- & Java-Client
• Reports können vom Ajax- & Java-Client aus gedruckt warden
• Bis dato (QlikView 8.5) im Ajax-/Java-Client nicht supportete Objekte werden nun voll unterstützt.

Web-Integration:

Für die Integration in Inter- und Intranet werden nun „Web-Parts für Sharepoint Portal Server“ und „Workbench“ (eine Sammlung von asp.net Controls für die Einbindung von QlikView-Objekten in asp.net Applikationen) angeboten.

Service Release 1:

Seit dem 23. September 2009 ist der Service Release 1 (SR1) von QlikView veröffentlicht worden. Dieser Service-Release betrifft alle Produktversionen (Publisher/Server, Client, etc.).

Als zuverlässigste Variante hat sich folgende Variante herausgestellt:

[qvl]
//variable a holds true (=-1) or false (0)
LET a = IsNum(NoOfRows(‘TABLE_NAME’));
[/qvl]

Dies liefert true (= -1) retour, wenn die Tabelle existiert, false (= 0), wenn die Tabelle nicht existiert.

Also z.B. nun in einer IF-Abfrage verwendet:

[qvl]
if (IsNum(NoOfRows(‘Dummy0’))) then
// do something here if table Dummy0 does not exist
end if
if (not IsNum(NoOfRows(‘Dummy0’))) then
//do something here if table Dummy0 exists
end if
[/qvl]

Im nachfolgenden Beispiel können Sie dies nochmals austesten.

Dies entspricht der bisherigen Client-Version des „QlikView-Developers“ ohne Einschränkungen in der Funktionalität (die unterschiedlichen Varianten des QlikView-Clients mit „QlikView Analyzer+, „QlikView Professional“ und „QlikView Developer“ gibt es nicht mehr).

Die „QlikView Personal Edition“ in der jeweils letztgültigen Version kann man unter http://www.qlikview.com/download/ downloaden.

Natürlich muß es irgendeinen Unterschied zwischen der käuflich erworbenen Lizenz und er „QlikView Personal Edition“ geben:

• Es ist nicht möglich, Dokumente zu öffnen, die von einem anderen Benutzer erstellt wurden
• Ein Benutzer mit einer Vollversion jedoch eine Datei öffnen, die mit der „QlikView Personal Edition“ erstellt wurde; ebenso können diese Dokumente über einen QlikView-Server/Publisher geöffnet werden

Technisch gesehen, wird mit jedem QlikView-Dokument auch ein User-Key generiert, welcher Identifikationsmerkmale des Benutzers und des Compters beinhaltet.

Nun stellt sich natürlich die Frage, was passiert, wenn man den Computer wechselt oder diesen neu aufsetzt: auch da hat sich QlikTech was überlegt:

• es ist zwar nicht möglich, die zuvor erstellten Dokumente zu öffnen,
• man wird jedoch gefragt, ob man die Dokumente wiederherstellen will; dann wird ein neuer User-Key erstellt, der für nachfolgende Dokumente verwendet wird; QlikView akzeptiert insgesamt viermal diesen Vorgang

Was bedeutet dies nun in der Praxis:

• Durch die Aufhebung der Produktunterscheidung von Analyzer+, QV Professional und QV Developer kann man innerhalb einer Organisation nicht mehr so leicht die Rollen einfach durch die Lizenz unterscheiden.
• Es ist jedoch möglich, über Registry-Keys die Funktionalitäten für einzelnen Benutzer einzuschränken.
• Als QlikView-Berater stehe ich vor der etwas problematischen Situation, daß ich unter Umständen Kunden in der Testphase Dokumente zum Tests (Demos, etc.) zur Verfügung stellten will; wenn diese die „QlikView Personal Edition“ verwenden, dann habe ich in Zukunft ein Problem
• QlikTech selber hat zum Teil auf diese Problematik bereits reagiert; auf http://community.qlikview.com werden seit einiger Zeit Demo-Applikation von QlikTech und anderen QlikView-Fans zur Verfügung gestellt; da diese Dokumente niemand mit der Personal Edition öffnen kann erstellt QlikTech nun spezielle Lizenzen, die dies ermöglichen; die Dokumente werden dann mit „*** This application is Personal Edition compatible ***“ gekennzeichnet (siehe dazu auch der Eintrag von Jason Long „Share QlikViews and QlikView Personal Edition“)

Fazit:

Sicher eine interessante Idee von QlikTech das Produkt für jedermann für den Privatgebrauch kostenlos und zum unlimitierten Test zur Verfügung zu stellen. Dies sollte zumindest einiges dazu beitragen, die Hemmschwelle zu senken, QlikView auszuprobieren.

Näheres wird die Entwicklung der nächsten Monate zeigen.

Von folgenden Demo-Daten bin ich gestartet:

Demo-Daten

Ziel ist es also eine Listbox mit Feld „b“ zu erstellen, wo die Inhalte von Feld „a“ in Zeile 2 und 9 nicht angezeigt werden.

Hmm, ja: es gibt keine Option in QlikView bei den Eigenschaften der Listbox, wie man Null-Values ausblenden kann, die Lösung war dennoch relativ schnell gefunden:

Nachdem sich in einer Listbox auch eine Formel für die Anzeige der Werte eingeben läßt, könnte man ja mit IsNull die Werte rausfiltern, mein erster Ansatz war daher folgende Formel für die Listbox:

[qvl]
if(not IsNull(b), b)
[/qvl]

Dies funktioniert jedoch leider nicht; offensichtlich werden die Werte in meinem Beispiel von QlikView nicht als Null-Values sondern nur als Empty-Strings interpretiert, daher habe ich die Formel etwas erweitert und schon funktioniert es:

[qvl]
if(not Isnull(b) and b <> ”, b)
[/qvl]

Achtung:
Ich wurde in einem Kommentar zu diesem Artikel richtig darauf aufmerksam gemacht, dass 64bit-Rechner so Ihre liebe Mühe und Probleme mit IsNull() haben, daher ist die beste Variante dies abzufangen wie folgt (dies funktioniert dann auf 32bit und auf 64bit Maschinen):

[qvl] 
if(LEN(TRIM(b)) > 0, b)
[/qvl]

Fazit:

Die Überprüfung auf Null-Values UND Empty-Strings scheint daher eine recht gute und zuverlässige Methode zu sein.
Die Überprüfung auf die Länge des getrimmten Strings scheint daher eine zuverlässige Methode zu sein.

P.S.: Sollte es eine einfachere Methode, die ich übersehen habe, dann freue ich mich über jeden Hinweis …

Die Lösung ist einfach:

Unter Felderliste die zwei Systemfelder „$Field“ und  „$Table“ eintragen und schon funktioniert es:

Suchen in Systemfeldern mithilfe der Suchbox

P.S.: Die Felder sind mit einem Strichpunkt zu trennen, unter Felderliste in den Eigenschaften der Suchbox muß daher $Field;$Table; stehen …

Vor QlikView 9 musste man für jedes Feld, welches von Benutzern für die Auswahl/Einschränkung von Daten zur Verfügung gestellt werden soll, eine eigene Listbox zur Verfügung stellen, in QlikView 9 kann diese Auswahlmöglichkeit in einer Suchbox zusammengefasst werden:

Auswahl in mehreren Feldern vor und mit QlikView 9

Wie funktioniert nun diese Suchbox:
Sie können eine Suchbox wie jedes andere Objekt mit „Neues Objekt“ => „Suchbox“ (bzw. über das Layout-Menü) erstellen.

Einfügen eines Suchbox Objektes

Im Eigenschaftsdialog der Suchbox können folgende Funktionalitäten eingestellt werden:

Angabe, in welchen Feldern gesucht werden kann

Die Standardeinstellung ist „Alle Felder“, jedoch wird man dies kaum so verwenden, denn dies würde bedeuten,daß der Benutzer in allen zur Verfügung stehenden Feldern suchen kann; praktikabler ist es dann eher, wenn man die gewünschten Felder unter „Ausgewählte Felder“ hinzufügt, in meinem Beispiel die Felder „Firma“, „Region“ und „Produktname“:

Auswahl der Felder in denen gesucht werden soll

Sonstige Optionen (Reiter „Präsentation“):

Im Reiter „Präsentation“ des Eigenschaftsdialoges der Suchbox läßt sich weiters einstellen, ob

• in aktuell ausgeschlossenen Werten gesucht werden soll und
• ob das Suchwort hervorgehoben werden soll

Erweiterte Eigenschaften der Suchbox

Anzeige der Suchbox

Beginnt der Benutzer nun bei einer aktiven Suchbox zu tippen, so werden automatisch jene Treffer in allen definierten Feldern angezeigt, die zum Suchbegriff passen:

Tippen und suchen ...

Jene Suchmechanismen, die auch in Listboxen zur Verfügung stehen (Textsuche, numerische Suche, Wildcard-Suche, etc.) stehen auch hier zur Verfügung. Die Selektion der einzelnen Werte (auch Mehrfachselektion) ist wie in jeder anderen Listbox möglich.

Fazit:

Die Suchbox ist eine sehr praktische und funktionelle Neuerung in QlikView 9. Durch die Verwendung einer Suchbox läßt sich statt der Anzeige mehrer Listboxen viel Platz sparen, die einzelnen Sheets werden dadurch deutlich übersichtlicher.
Es ist fast anzunehmen, daß es in Kürze kaum noch QlikView-Applikationen geben wird, wo auf jedem Sheet die Suchbox Ihren festen Platz einnehmen wird.