Email a Report as PDF

In large companies its almost always needed to send bulk reports to employees with various settings. And I've often had to do more complex emails than what is possible with QlikView's PDF Module.

The add-on is nice if you want sysadmin to administer email recipients, but if you need to do anything more advanced, like having multiple fields looped, have external recipients or simply think its too pricey. Then I recommend you do it the old fashion pre QlikView 9 way.

Change the port for QlikView Administration Console

Many large organisations don't allow traffik on port 4780. But they allow it on 80. So to remotely administer your QlikView server, you easely change the admin port.

Open the C:\Program Files\QlikView\Mangement Service\QVManagementService.exe.config with an TextEditor. Look for the xml entity “QMSFrontendWebServicePort” which is set to 4780 and change this to 80.

Slowly Changing Dimensions

I was looking at examples of how other Qlik developers have implemented Slowly Changing Dimensions in QlikView. I found some examples, but most contained synthetic keys and did not take account for multiple products.

So I though I would do an improved example, which method easely could implemented in the real world. This can be used both for Type 2 and Type 3.

Overlook function: Set Reference

Yesterday I stumbled upen a very neat function that seems to have been overlooked by myself.

Its called Set Reference, it can be invoked by right clicking on any visual chart object, and selecting "Set Reference" from the contextual menu. When then change the selection, the version from when you selected the reference is then shown dimmed in the background. Very usefull.

From the QlikView manual:

3 Things that will help to spark curiosity

Shawn Helwig has written an very interesting article on how he suggest that QlikView can spark a culture of curiosity. While he has some very valid points I would like to dig deeper and apply some theories of knowledge management to it. And at the end I've have 3 recommendations that can help to spark curiosity.

QlikView does not fully support Alphanumeric sorting

I've just talked with support about a sorting issue where QlikView sorts alphanumeric strings differently than MSSQL or Excel.

Consider these strings:

2008 Vielen Dank
002010 Yes Men
2009 Salut
2013 Ja tak!
002010 QUI
2012 Verde

Try sorting them with Text() and order by in QlikView that would give:

2008 Vielen Dank
2009 Salut
002010 QUI
002010 Yes Men
2012 Verde
2013 Ja tak!

The above is however wrong, in an Alphaumerical sense. Correct sorting would be:

Version Control support is coming in QlikView 10!

Ohh My God! A year ago I submitted this feature request for 10:

>>When doing a more "professional" developing it would be nice to do version control and be able to track development done by the different project members... I don't want to check in the binary QVW file. I would rather check in the script and control definition files... If Qlikview consist of two parts. The data repository and a XML script and object definition file containing the definition for all objects. It would be easier to have a multi developer development in a controlled fashion.<<

How to test if a variable exists or not

Ever since leaving PHP coding for QlikView - I've always missed the Handy isset function to check if a variable, well, isset. But its actually possible to use the isnull function to do the same in the script. Just remember not to use $ sign expansion! Since this will evaluate the variable, before its passed into the function.

This works nicely:

If isnull(foo) Then
    Let foo = 'bar';
    trace now it exists;
    trace exists;    
End if

Log Helper Function

In time in PHP programming we often did a "Helper" function. A helper does something genericly easier than the code provided by the application. For most parts we had a helper application that we used for logging certain errors into a database.

With QlikView I've started to setup a LogHelper to help log what goes on in my Collect scripts. This is hugely helpfull since it provides a log of how the load script have performed historicly, and I can easely see if there was a hickup at one of my clients.

Generic Delta load functions for tables with timestamp and id

When loading data from many tables, and when its necessary to do more than the usual store and drop commands. You often end up with a lot of code that does stuff: Like fetching last timestamp for the load or storing the incremental data. And quite a lot traces to make its works correctly.

So I tend to write some functions for this which lets you save many line of code and make maintenance so much easier.

These functions should work generically for any tables that both have a modified date and an auto incrementing id fields.

#1 Excuse for slacking off


I've read XKCD for some time now and stumbled across a version that I had to hack into a QlikView compatible version:

QlikView 10 preview: Linked Objects

One of the really big things for Enterprise developers like me is being able to create master objects, so you easely can propergate changes across a QlikView file. So in QlikView 10 they introduce Linked Objects.

You can create a linked object by dragging and holding shift + ctrl down, or you can copy it and paste it as Linked Object.

This essentially means that you can create a single menu and copy that to your other sheets, and then when some must be added or changed, the change will propergate to any object that is linked to those menu objects!

QlikView 10 preview: Moving stuff around

We have just started beta testing of QlikView 10, and some nice feature small improvements have been made together with all the big ones:

You can now move an item that has no caption by holding Alt and dragging.

This even works when a object has Allow move/resize unchecked.

You can use click and then press ctrl +shift to create a linked object. A linked object will let you have unlimited copies of a object, but if you change any properties one either the master or any of with linked items, then they will propergate to all the linked items!

Change Move/Size setting macro

In any qlikview application the object setting Move/Size is rather important, should the user be able to move the objects around? For most of the charts it might be good to be able to open more then one chart at the time. But for the menu objects and f.ex. the current selection box this isn't the all to good. At first I used the server setting "disallow moving and sizing object" but when i developed a new application i needed to uncheck this setting. After this i needed to change (manually) all of the object settings.

Developing in Multi Environment

Do you work in multiple environments like sandbox, test and production?

With the builtin function computername() and a few if statements the need to change your code and database connection each time when moving to another environment is eliminated. The code snippet below is taken from one of my Collect.qvw files.

Fixing latest Citrix on Mac

This works for Firefox, Safari and other browsers. And fixes the mess Citrix made with its latest release.

Mac OS X users running the latest version of the Citrix ICA client may encounter difficulties launching ICA files dished by a Web Interface site.  Citrix is aware of the issue, but they do not have an ETA on when they might issue a new client to fix the problem.  Until then, follow these instructions to restore all functionality:

1.  Close all open web browser windows

Use QlikView to read an active directory

Its really easy to use QlikView to read out a Active Directory. Here is how:

Simple use a OLE DB connection and the "OLE DB provider for Microsoft Directory Services" which should be default installed in must windows computers if not google for the driver.

In the connection tab, set the Data source to: LDAP:// < where the last part is any AD node in the network.

And use any username that has the rights to read the AD

In the edit script place the lines below:

How to setup a secondary QlikView server to do reloads

 With the release of QlikView 8 it became possible to setup of split QlikView servers, one that handles reloading of documents and one the serves documents. With QlikView 8.x this was a daunting task, where many .xml config files needed to be edited. Now with QlikView 9 it became manageable setting it up. Only a little tweaking is needed and no more xml config file editing.

Restart QlikView services in version 9


If you want to restart your qlikview services running QV 9 periodically put this into a bat script:

REM Shut it down:

NET STOP "QlikView Directory Service Connector"
NET STOP "QlikView Distribution Service"
NET STOP "Qlikview Management Service"
NET STOP "QlikView Publisher Command Center Service"
NET STOP "QlikView Server"
NET STOP "QlikView WebServer"

REM Turn them back on:

QlikView Server 9 SR 2 installation on Windows Server 2008

This is a guide suitable when you are about to install a QlikView Server 9 SR 2 on a Windows Server 2008 machine. 

Recommended prerequisites:

• Domain Service account, with never expiring password, which may look up users in the AD.

• A local administrator account for the server, which will perform the install (you can use the service account).

• Server with preferably static IP and a hostname.

Syndicate content