QlikView

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.

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.

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.

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.

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:

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

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;
Else
    trace exists;    
End if

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.

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.

Hi,

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

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://your.adserver.com < 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:

Hi,

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:

The Built-in webserver in QlikView 9 can be used for redirections of url's. This example will show how you access the accesspoint by just typing in the hostname for the server and nothing else. The QlikView Webserver will now show the "Accesspoint"-content also on the "root" (hostname/)

I know that this has been a lot talked about. A good text editer with syntax highlighting for QlikView, why on earth does this not exist!? Follow these simple instructions and you will get it. I've been working a few months on getting this right. QlikTech sadly has no xml file with the whole syntax and functions in it. So that I had to copy paste and edit the whole thing from the manual, also I'm slowly working on a fully supported autocompletion function but this might take a while.

I've wanted to write about this for a long time. You can really do some cool stuff with the ajax tools and regular html pages.

So what I want to do is to add some QlikView objects to a regular html page, and have them behave just is in QlikView. When I select something i one box then it propergates the selection to the others.

I was discussing with QlikTech which was the fastest method of adding a small relational table to a table. Basicly you have 3 options: Table join, Mapping load and If(). The table join method is discarded on forehand because it will run though every row in the loaded table. The mapping load method has two methods either you can in your load syntax use ApplyMap(Field,Map1) or afterwards do a Map Field Using Map1. The last method is doing a simply If inside your load. According to QlikTech the two Mapping methods should be the fastest.

I’m working on the QlikView combo certification and have noticed this minor bug in the LOAD * RESIDENT syntax.

AcctInvoiceReceiptstxBillingTreat:  
LOAD KEY_MEDNO,Treat.TX,KEY_INVOICE,
KEY_ACCTNUM,Treat.MNAME RESIDENT tab1 ;

If I do the above, a table with the label AcctInvoiceReceiptstxBillingTreat is created.

If I however do a * load then

AcctInvoiceReceiptstxBillingTreat:  
LOAD 
* RESIDENT tab1 ;

The table created is labelled tab1.

QlikView publisher enables you to create a distributed version of a QlikView document that contains reduced data. This is handy when you have a master document with all data in it, and you want to deliver it to users who should not view all data. But its really unusable from the perspective that you cannot make receive users from an external database like your ERP. Unless you manually wish to enter them one by one.

In the past we have used a script to insert these users into the Publisher MSSQL database. But this is tricky and not the most reliable method.

Coming from basic programming where you write every line of code yourself and collaborate with a lot of people in the same project. It becomes completely mindboggling that no one uses version control software with QlikView.

Many applications are super simple, so one might argue this is overkill. But QlikView is moving into the enterprise, and changes are made often, so proper version control and collaboration quickly because a most. At least to avoid the most common mistakes like overwriting others code.

Here's the new face of QlikView: