Creating a QlikView Extension from Scratch using D3 (QlikTip #50)

I recently received the request from a colleague if the following animated scatter chart published on GapMinder could be realized with QlikView Extensions.

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:

Unable to display content. Adobe Flash is required.

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.

Bookmark and Share

Tags: , ,

Better Current Selections Box to Translate & Hide Fields (QlikTip #49)

This article demonstrates how to overcome some limitations of the standard Current Selections Box of QlikView: Translating field values, translating “hardcoded” labels of the Current Selections Box, hiding specific fields and customizing the icons shown.
The solution is based on a QlikView object extension I have created, called “Better Current SelectionsBox”.

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

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:

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

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

Property Setting Description
Fields FieldName Select the field name, FieldName is my example above
Expression =$(='FieldName_$(=vSelectedLangKey)) For the example above this will produce “FieldName_EN” if “English” is selected, “FieldName_DE” if “Deutsch” is selected

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


If a translation for a field name is not available the standard field name will be shown (default fallback mechanism).

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'

Property Example Description
Hide Prefixes _,%,HF Hide Prefixes, separated with a comma (,). A single HidePrefix can be a single character or more characters (as shown in the example above with “HF”, so all fields starting with “HF” will be hidden)
Hide Fields Languages,OtherFieldToHide Define the fields (by setting their field names) to be hidden 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

Property Description
Show Header

Show/Hide the column header
Show Values

Show/Hide the values of the current selection
Show Clear Icons

Show/Hide the clear icons
Note: If choosing this value, any standard selections box has to be place on the current sheet
Show Lock/Unlock Icons

Show/Hide the lock/unlock icons
Note: If choosing this value, any standard selections box has to be place on the current sheet
Show Status

Show/Hide the icon indicating the current status (selected/green & locked/blue)

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:

If you choose “Show Clear Icons” and/or “Show Lock/Unlock Icons” place any standard “Current Selections Box” on the current sheet. You can hide the object (Show Condition = 0), so it will not be shown to the user, but the “Better Current Selections Box” relies on any Current Selections Box on the current sheet. Unfortunately …

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.

Bookmark and Share

Tags: , , ,

QlikView Mashup Examples & Documentation (QlikTip #48)

Creating Mashups in QlikView is extremely simple but I am very often surprised that QlikView’s ability to integrate QlikView documents and objects into other web application is not used very often, sometimes it is not even known that this can be achieved easily.

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:

A mashup, in web development, is a web page, or web application, that uses and combines data, presentation or functionality from two or more sources to create new services.
The term implies easy, fast integration, frequently using open application programming interfaces (API) and data sources to produce enriched results that were not necessarily the original reason for producing the raw source data.

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

Bookmark and Share

Tags: , , , ,

Reloading QlikView Documents using the Ajax-/Mobile-Client (Triggering EDX Tasks by using a QlikView Extension) (QlikTip #47)

It is quite easy to reload a current QlikView document if you are using the IE-Plugin or QlikView Desktop. But achieving the same result if you are using the Ajax-/Web-/Mobile-Client is a bit more demaning.

The solution described here closes the gap using a combined approach of misc. QlikView integration techniques:

  • The QMS API (QlikView Management Services API)
  • A new QlikView Object Extension called “CallEdxFromAjax”

Screenshot using CallEdxFromAjax QlikView Extension:

Architecture Overview:

You can find the source-code, detailed documentation, install-files on a dedicated project site on CodePlex.com.

Bookmark and Share

Tags: , , , , ,

CSS3 Cheat Sheets for QlikView Extension Development (QlikTip #46)

I have already posted my favorite cheat sheets for jQuery. Today here are my favorites for CSS3 (Cascading Style Sheets v3):

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


http://www.css3.com/cheat-sheets/

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


http://coding.smashingmagazine.com/2009/07/13/css-3-cheat-sheet-pdf/

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


http://downloads.gosquared.com/help_sheets/07/CSS%20Help%20Sheet.pdf

Compatibility Cheat Sheet

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

Compatibility Tables by CanIUse.com


http://caniuse.com

CSS3 Selectors Explained

This site explains CSS3 selectors in detail:

CSS3 Selectors Explained


http://www.456bereastreet.com/archive/200601/css_3_selectors_explained/

Bookmark and Share

Tags: , , ,

A Comprehensive List of QlikView Object Extensions (01/2013)

In the last few weeks I have dedicated quite a lot of time to extension development in QlikView. Doing so I have certainly reviewed existing extensions to get new ideas and study existing code. Unfortunately there is no central repository available where you can find all extensions.

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

QlikView Object Extensions

I have only added extensions which work in QlikView 11. If I have forgotten some, please let me know and I’ll add them.
Document extensions and GeoAnalytics-extensions will be covered in another upcoming article.

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

Navigation Bar
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!”

VideoPlayer QlikView Extension
Properties of VideoPlayer  Extension
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

Bookmark and Share

Tags: , , , , , ,

jQuery Cheat Sheets for QlikView Extension Development (QlikTip #45)

When developing QlikView extensions you will probably use jQuery, too. jQuery is also used by default by the QlikView Ajax client so jQuery is available out of the box. For all QlikView Extension developers who are not working with jQuery every day these cheat sheets may help:

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

Bookmark and Share

Tags: , ,