QlikView on Amazon Kindle

I spotted a photo floating around the web earlier of what looked to be QlikView running on an Amazon Kindle (the original not the new touch screen Kindle Fire), so of course I had to have a go myself. I grabbed my Kindle and fired up the web browser, which Amazon describe as "Experimental". Obviously this wouldn't be the richest QlikView experience ever but would it be usable at all?

I first hit the QlikView demo site which seemed to load fine. I chose and app and fired it up.

Apart from a lot of the text appearing surrounded in black, it was just about readable and everything appeared to be there. Now for the real test, can I make a selection?

Yup, after a slightly slower than normal refresh, the selection seemed to be applied and the charts displayed the updated results.

I've yet to find a way of right clicking or clicking and dragging within charts, so functionality is limited and it wouldn't be my first choice of mobile device but at a push it would be usable with nothing else to hand.

QlikView Collaborative Sessions Video

Whilst trawling Youtube I found this very cool video demonstrating collaborative sessions in QlikView.

For those that haven't upgraded yet, collaborative sessions are a new feature in version 11 and allow more than one user to share a single session. Imagine a webex where any user can take control.

QVSource Review

I finally got some time this week to sit and play with QVSource, produced by the guys at Industrial CodeBox. It's something I've been meaning to get round to for some time but other things kept getting in the way. Below is a quick run through of my first play with it and what I thought.

What is QVSource?

On its website (qvsource.com), it is described as follows:

"QVSource makes QlikView the best connected Business Intelligence platform available and enables a new range of QlikView applications to be built which mashup and provide insight into data from a wide range web APIs and other data sources not natively supported."

In simple terms, it allows you to access a growing list of data sources which would normally take some custom development to integrate into QlikView. At the time of writing, it can pull data from 32 APIs including Facebook, LinkedIn, Twitter, Youtube, Google Analytics, even OData and POP3 email accounts.The list keeps growing too.

How does it work?

The basic principal is simple, QVSource plays middle man between QlikView and the APIs. QlikView can natively make web calls to return data. QVSource runs a small web server which QlikView can make a call to and it then handles communication with the relevant API returning the data to QV.

Getting it working

I was provided with a download link where I could obtain 2 things, the software and a license key file. The software comes as a zip file and on unpacking I discovered no installation appeared to be necessary. OK, time to read the documentation maybe? Don't be silly I'm an impatient techie, I just want to play and besides, I figure trying to do it blind would be a good test of how easy it is to use. For those that do wish to read up first there is a Getting Started Guide explaining in detail the things you need to know to get off the ground.

I fired up qvsource.exe and was presented with an initial window like this:

First things first, how to apply the trial license I had been given. Clicking on the License tab presented a screen displaying that I had no license installed and a button to the right labelled Apply License. Clicking this popped up a window with which to find and apply the license key file (.lic) I download earlier. Once applied everything looked set for me to crack on with getting some data into QV.

Moving back to the Connectors tab I decided to play with Twitter first. I selected the Twitter Connector and hit the Configure button to the right of the window. This popped up a larger window with what, at first glance, looked to be rather a lot of settings.

No worries, a helpful link towards the top right labelled Help Page For This Connector took me to the relevant Wiki page (so much for not reading the help) and it didn't take me long to work out my next step was to hit the Authenticate button. This wasn't initially obvious as it appears to the right of an input field and so I assumed I needed to enter a Token before clicking it. Maybe a slight interface tweak for the future but no biggy. Clicking Authenticate popped up a new window asking me to enter my Twitter username and password.

After entering them and hitting Authorise App I was presented with a Pin Code. The box at the bottom left asked me to enter the pin before closing but it had already done this for me.

Closing this window took me back to the Twitter Connector window and the Token and Token Secret fields were now populated.

I populated the Search Term, Users Lookup and Username fields in the lower half of the window with total guesses and then clicked on the Connector Data tab at the very top. At this point things started to look very promising and the screen showed a list of tables of data available to me from the Twitter API.

I chose the Search table as it was top of the list and after QVSource did it's thing I was presented with a table of data showing tweets matching the search term QlikView as I had entered in the configuration screen. But what next? How to get this data into QV? Then I noticed the QlikView Load Script tab.

This tab offered me a ready made Load statement to cut and paste into QV. Leaving QVSource running in the background so it could work it's magic I quickly pasted the Load statement that had been generated into the script of a new QVW and hit Reload. It was as simple as that and I had Twitter API data in QlikView. I quickly built a couple of objects in QV to prove all was as expected.

I performed this test with QV10 but have since also tried with with QV11 and there is no reason it shouldn't work for older versions of QlikView also. I also played with some of the other tables of data available through the Twitter API with equal success.

Pro's

Where do I start? The obvious benefit is in being able to pull data from sources which in the past would have proved difficult. QVSource pays for itself in the time saved doing this alone.

As you can see above, it's pretty simple to use too. Anyone with a basic understanding of QlikView should be able to get things up and running in a matter of minutes. Without reading much documentation I had it pulling data from Twitter in under 10 minutes.

If you do get stuck then the documentation is second to none with a Wiki dedicated to QVSource covering the entire product including how to get the individual connectors working. This documentation you might need, as despite how simple QVSource is to use, some of the web APIs can be a little tricky to get running if you are not familiar with them. Following the Wiki solved any trouble I had and I've since also pulled data from Facebook, LinkedIn, Google Analytics and a mailbox, all without too much trouble at all.

Con's

Well, not a lot really, I had to scrape the bottom of the barrel to find these. Remember though that this is a very new product and so I'm sure the development Industrial CodeBox are doing will continue to improve it going forward.

The first thing to note is that dealing with large volumes of data will be very slow. This isn't a limitation of QVSource however and rather a limitation of the speed with which data is returned from the APIs. Bear in mind that the data will also be pulled over your internet connection and so again this could prove slow. Most APIs limit the amount of data they will pass anyway.

I found dealing with multiple instances of the same connector to be a little tricky too. For some connectors, such as the MailBox Connector, the authentication details must be entered into QVSource rather than in the QV script. Because of this you would need a seperate instance of QVSource running for each mailbox you wish to read data from. This isn't too hard to achieve by simply copying the QVSource folder repeatedly and configuring each instance to run on a different port (a setting on the Advanced tab). Moving the authentication details to be part of the request URL in the QV script would make this work with a single instance of QVSource and this is something that has already been done with some of the connectors such as the Facebook Personal Connector. I'm sure its something they'll get round to implementing for all connectors at some point soon and will drop the guys an email to make sure I'm not overlooking an obvious solution.

Also, the QVSource application needs to be running in the background for QlikView to refresh the data. This is fine when using it with QV Desktop but when running in a server environment, QVSource needs to be able to recover if the server is rebooted or more importantly continue to run if the user logs out. There is however a relatively simple work around to this issue using the Windows Scheduler as you can read here. Maybe the future could bring a version which runs as a service with a web control panel as this would bring it inline with QVS architecture.

Conclusion

The power of being able to analyse social media and web traffic data is unquestionable for marketing companies and departments (possibly even for social recruiters). QVSource coupled with the power and ease of QlikView makes doing just that a very quick and simple task.

Would I recommend it? Without a doubt!

SET vs. LET

I got into a conversation with someone today who was struggling to understand the difference between SET and LET. It wasn't the first time I've had this conversation so thought I'd explain it here too in case any QV newbies were also struggling with it.

SET is taken literally and what is on the right of the equals is written into the variable. So writing:

SET vMyVariable = 5*2;

Would result in vMyVariable containing "5*2".

LET is used when you wish QlikView to equate what is on the right of the equals and write the answer into the variable. So writing:

LET vMyVariable = 5*2;

Would result in vMyVariable containing "10".

Simples!

Checking if a File Exists

A question that comes up fairly regularly from customers and also on the QlikCommunity is how do you check if a file exists before loading it? As with everything in QV there is more than one way to do this. Below I'll demonstrate the 3 methods most commonly used in QV although there are without a doubt more. 

Personally I would use method 1 if dealing with a single QVD file and method 2 for all other files or when dealing with wild cards in file names. I would avoid method 3; although it is a completely valid method, in my opinion, it belongs within a much more complex custom error handling routine which is outside the scope of this post.

Method 1 - qvdCreatTime()

This method uses an inbuilt function within QV called qvdCreatTime() which returns the date and time the QVD was created. If the QVD doesn't exist, qvdCreatTime() returns NULL and thus we can use it to check if a QVD exists. Obviously this method is limited to QVD files only. It also cannot handle wild cards.

IF (NOT isNull(qvdCreateTime('myqvdfile.qvd'))) THEN
    LOAD * FROM myqvdfile.qvd (qvd);
END IF

Method 2 - For Each File in Filelist()

This method uses the filelist functions within a for loop. This function is only possible within the loop and has the benefit that wild cards can be used within the file name and path. This allows you to load multiple files that match a pattern by looping through them. Assuming the field names are all identical the files will be concatenated into a single table. Unlike method 1 above, this method can be used on any file type.

FOR each File in filelist ('myqvdfile.qvd')
     LOAD * FROM myqvdfile.qvd (qvd);
NEXT File 

Method 3 - ErrorMode & ScriptError

This method isn't strictly checking if a file exists but rather changing the way QlikView reacts to errors if it tries to load a file that it can't find. To do this we change the value of the ErrorMode variable to 0 which prevents QV from stopping the script execution if an error occurs. We then try to load the table and afterwards check the variable ScriptError to see if an error has occurred. If ScriptError has a value of 1 then no error occurred. A value of 8 denotes that the error that occurred was that the file does not exist. We can replace the TRACE statements with the relevant actions to take. We then set ErrorMode back to it's default value of 1 and continue the rest of the script.

SET ErrorMode = 0;

LOAD * FROM myqvdfile.qvd (qvd);

IF ScriptError>1 AND ScriptError<>8 THEN
    TRACE File not found;
ELSE
    TRACE File loaded;
END IF

SET ErrorMode = 1;

QlikTech Release Pricing

In a very bold move, QlikTech today released their full price list publicly on their website.

http://www.qlikview.com/us/explore/pricing

This certainly sets them apart from most software vendors especially those in the BI space. Will it have any effect though? Time will tell.

Compression in QV

I was talking to someone this morning who mentioned how amazed they were at the compression QlikView managed to achieve. This seems to be a comman misconception about QlikView, so after explaining this to him, I thought I'd repeat my explanation here also.

FACT: QlikView does not use compression for QVDs!

So why then is a QVD so much smaller than a CSV containing the same table of data or why is a QVW so much smaller than several QVD's concatenated together into a single table? The answer lies in understanding how QlikView stores data and the key to this is understanding some basic facts about QlikView's Associative In-Memory Technology. This is what QlikTech have to say about it:

"QlikView uses an associative in-memory technology to allow users to analyze and process data very quickly. Unique entries are only stored once in-memory: everything else are pointers to the parent data. That’s why QlikView is faster and stores more data in memory than traditional cubes."

So from this you can easily deduce the reason QlikView appears to compress data so much is that it stores only unique values thus removing repetition in the data. Think about it for a second, if I have a million rows of data but a field contains only 10 unique values, then QlikView stores those 10 values only and not a million values.This applies equally to the RAM QlikView will need to hold the data as well as the hard disk space needed to store the QVW and QVD files. 

You can even use this fact to your advantage when dealing with large volumes of data in QlikView. Breaking down a field into several fields can cause more repetition and thus reduce the amount of resources QV needs to hold the data both in RAM and on disk. Email addresses are prime candidates for this technique as the domain part will likely not be unique to one entry. Take this example:

EmailAddress
bob.jones@mycompanydomain.com
jane.smith@mycompanydomain.com
paul.hill@mycompanydomain.com

QlikView will consider each of these values as unique and thus will be storing the "@mycompanydomain.com" part repeatedly. If you were to split the email addresses at the "@"  then the domain part becomes the same for all 3 records and thus QV will store it only once. You can split the email address using the following lines within a load statement:

....
left(Email,index(Email,'@')-1) AS EmailName,
right(Email, len(Email)-index(Email, '@')) AS EmailDomain
....

When wanting to display the email address in a chart you only have to concatenate the 2 fields together remembering to replace the '@' in the middle like this:

EmailName & '@' & EmailDomain


The same technique can be used for mail addresses, phone numbers, part codes and any other patterned, repetitive data.