Miami here I come

It's that time of year again where I am looking forward again to "Qonnections", the QlikView global partners conference.  This will be the 3rd year in a row where we will be at the Fontainebleau Resort in South Beach, Miami Beach.  It kicks off on Sunday 22nd April and finishes up on the following Wednesday.

I have to say that my favourite Qonnections was 4 years ago in the Lowes Hotel, also in South Beach. There were a number of reasons:

 - Jonas Nachmanson, then CTO, announced Set Analysis coming in version 8.5. The techies in the audience gave the announcement a standing ovation.

 - Stephen Few delivered the keynote speech on the first morning. This changed pretty much everything about the way I have delivered QlikView projects since then.

 - Capricorn Ventis won the award for the fastest growing partner. It was a great experience to win an award at an event like this.

 - I attained certification in the brand new "Professional" Certification program. Earlier that year I had been one of the first people in the world to achieve the new "Developer" Certification.

So, there were a lot of highs that year. I would have to say that subsequent Qonnections were somewhat lesser in the number of highs.

I think that maybe, for me, it is that Set Analysis was the last "great" innovation in QlikView. There have been a lot of "nice" features in subsequent releases, and good features to make the product more enterprise ready, but none of them changed the way that I used the product so fundamentally. I don't think that there has been 1 .qvw that I have created in the last number of years that has not used Set Analysis.

I have an idea that this year might have some more interest. I think that v12, whose features (without promises of final delivery) will probably be announced to partners next month. I don't know, but I suspect that there will be some really interesting things happening and perhaps some fundamental changes. Of course, I will be restricted from commenting on them until they are made public, but I might whet your appetite.

Enjoy the tune - Will Smith, Miami

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Where are the Irish going? Another poor pie chart.

Oh dear.  Yet another poor example of a pie (in fact a doughnut) chart to try and get across something that is very important to Irish people.

David McWilliams is a leading Irish economist.  One of the few to predict trouble many years before the crash, mostly not listened to.  I think that he has a wonderful grasp of world economics and how they apply and interlink with Irish economics.  Unfortunately, when he described the chart below as a "wonderful graphic on Irish emigration", he showed that, like many, he doesn't know much about the presentation of quantative data.

The chart in question is found here:  http://www.locusinsight.com/Ireland_files/Emigration/chart.html

It is an "interactive" doughnut chart where each segment represents the number of people going to different locations around the world.

Besides all the usual issues with pie type charts (my blog post on "Defending Pie Charts" might be a good place to start reading about the problem with pies; and of course, Stephen Few's Save the Pies for Dessert), the whole interactive thing makes it even more difficult to grasp the numbers and the relationship between them.

I grabbed the numbers from the chart into QlikView and created a much simpler, but far more effective, line chart:

(click for larger image)

From this chart, it is quite easy now to see that there is a general trend upwards in emigration and the UK and Australia are the main destinations.  There is a huge spike in EU12 (the 12 countries that have joined the EU since 2004) during 2009.

I had my suspicions of what that was so I went to the Irish Central Statistics Office website and retrieved the figures for emigration by nationality.

(click for larger image) 

This confirmed what my suspicions were - many of the eastern Europeans (Polish, Lithuanian, Latvian, etc.) who had emigrated into Ireland during the Celtic Tiger years, many of whom worked in the construction industry, left in 2009 because of the collapse of that industry.

"Fancy" charts often actually hide the numbers that people want to see.  Here we have two very plain and simple line charts that tell much more of a story.

It is not a great story if you are Irish.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

What are the colour of your eyes?

I often find myself reading other blogs, in the name of "research", and finding myself jumping from one link to another, following one idea to another, storing lots of fascinating facts within my grey matter (in theory).

One of my favourites is Robert Kosara's Eager Eyes blog.  Recently, he was discussing writing as a metaphor for visualisation which led me, via a couple of hops, to an IBM research piece called "How NOT to Lie with Visualization", written by Bernice E. Rogowitz and Lloyd A. Treinish of the IBM Thomas J. Watson Research Center, Yorktown Heights, NY.

The article is a discussion of colour maps used in visualisation.  It is worth reading.

My own rule of thumb for a colour map is to use just the one hue - e.g. Blue, because it is relatively neutral - and change the lightness of the colour from one end of the scale to the other (e.g. using ColorMix functions in QlikView).  If I need diverging scales, then use two different hues that go from light in the centre to dark at the extremities.  For this, I always imagine an old ordinance survey map that shows sea and land - as the sea gets deeper, the blue gets darker; as the land gets higher, the brown gets darker.

Of course, there is always the question of ability to discern the difference in colours in between those extremities and whether a colour map is suitable at all - some humans are much better than others.  So what can we do for those of us whose eyes are not so good?

In many cases, I will implement a simple rule - anything that needs investigation based on the business rule gets coloured, everything else stays neutral.

Move your QlikTech ProgramData folder

There are a number of reasons why you would want to move the C:\ProgramData\QlikTech folder to somewhere else.  For example:

-  Disk size issues 

The C: drive too small and the QlikView performance logs, reload logs, etc. are filling it up.

-  Failover

You have a "cold" standby server and it would be much better to have the ProgramData\QlikTech folder on the SAN drive so that when the cold server boots up, it will already have all the settings, schedules, etc. from the old server.

There are ways of moving the ProgramData folder - the supported one from Microsoft (with caveats!) is to do it during the installation of Windows - http://support.microsoft.com/kb/949977

This isn't really going to be an option for you if the server is already built by the nice friendly folks in the IS department.  They might also object to you trying to move the whole ProgramData folder anywhere else, just for QlikView.

I have run into this situation a couple of times now and there is a really neat solution - move the QlikTech folder to its new home and then create a symbolic link in the old ProgramData folder.  Everything will still work perfectly!

To do this:

1.  Stop all of the QlikView services.

2.  Move the QlikTech folder from C:\ProgramData to its new location, for example E:\ProgramData.

3.  Run a Command Prompt in Admin mode.  CD to the C:\ProgramData folder and run the following command:

     MKLINK /D QlikTech E:\ProgramData\QlikTech

4.  Restart all of the QlikView services.

All done.  Do the same on the "Cold" server (you will need to bring down the "hot" server first) and create the same symbolic link to the same SAN location and it will work as if it was the "hot" server and no need to ship settings files.

Hopefully you will find this useful.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Printing Reports to PDF using PDFCreator

PDF Creator is a nice tool for generating PDFs and it comes at a very attractive price.  Like other tools of this ilk, it creates a Printer driver which you can print your documents to.  Unlike other drivers (especially at this price point), it gives you additional control on what is printed using a COM interface.  This makes is quite handy to use from VBScript - either inside or outside QlikView.

I am going to give you some starter code here on how to do this from within a QlikView document using a Macro.  You can also do this from a .vbs file that is external to QlikView.  Just note that if you are using a .vbs then you will still need an appropriately licensed copy of QlikView Desktop to run this.

*** All script here is completely unsupported by myself or anyone at QlikTech.  Use at your own risk ***

If you are going to use a .vbs then you will need to create your own ActiveDocument variable.  This is how I do that:


    Dim QV, ActiveDocument


    set Qv = CreateObject("QlikTech.QlikView")
    

    QV.OpenDoc Document,"",""

    set ActiveDocument = Qv.ActiveDocument


In this case, the variable Document contains the full path to my .qvw.  The 2nd and 3rd parameter are a QVUser and Password - if you have those in Section Access.  Once you have an ActiveDocument object, the code is the same between the Macro and the .vbs.

Usually when I do this, I am going to loop across all the value in a field.  For example, I may have a field called "Seg" and I want to run a report for each value in this field.

I may choose to clear all values first:

    

ActiveDocument.ClearAll

Either way, I can call the GetPossibleValues to get a list of all the values in that field:



    Dim FieldName
    
    FieldName = "Seg"


    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues

Now, I can loop through the values, select each one in the document and call a function that I have made called Print_PDF for each value:


    Dim i


    for i = 0 to mySelections.Count - 1


        Dim FieldValue


        FieldValue = mySelections.Item(i).text
        
        ActiveDocument.Fields(FieldName).Select FieldValue


        Print_PDF FieldValue, "My Report", "RP01"
        
    Next


Print_PDF is, essentially, using the reference code from the PDF Creator documentation:


Sub Print_PDF(FieldValue, ReportName, ReportID)


    ' Designed for early bind, set reference to PDFCreator
    Dim pdfjob
    Dim sPDFName
    Dim sPDFPath


    '/// Change the output file name here! ///
    sPDFName = ReportName & " - " & FieldValue
    sPDFPath = "C:\PDFReports"


    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")


    With pdfjob


      If .cStart("/NoProcessingAtStartup") = False Then
         If .cStart("/NoProcessingAtStartup", True) = False Then

          Exit Sub

         End if
         .cVisible = True
      End If


      .cOption("UseAutosave") = 1
      .cOption("UseAutosaveDirectory") = 1
      .cOption("AutosaveDirectory") = sPDFPath
      .cOption("AutosaveFilename") = sPDFName
      .cOption("AutosaveFormat") = 0 ' 0 = PDF
      .cClearCache


    End With

    ' Print the QlikView Report

    ActiveDocument.PrintReport ReportID, "PDFCreator"



    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    Loop
    pdfjob.cPrinterStop = False


    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing


End Sub


The only additional piece here from the PDF Creator documentation is the QlikView call to Print the report:


    ActiveDocument.PrintReport ReportID, "PDFCreator"

I pass the field value and a ReportName value into the function so that I can generate a different PDF file name for each field value.

Last thing is that you will need to enable System Access in the Macro settings.  In a way, this is where .vbs has an advantage.  Because the script is already external, there is no issue with enabling System Access.

Enjoy.  Just remember, this is all unsupported so use at your own risk.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Saving Space by Nulling Zeros

If reducing the memory requirement of your QlikView document is important, this tip might help shave a few bytes that could add up to a significant performance gain.

There are several documents on performance improvement that will suggest that you should shed fields that do not contain data that will be used.  My tip is to look at fields that has data that you do use and shed the data in those that you won't!

One example would be a numeric field that may contain a lot of zeros.  If the only use of this field is in a sum, then there is no different in that zero being there or not.  The only difference would be in a count or avg.  Therefore, if you have a lot of zeros in a field that will only be summed, consider transforming them to null() in your load.

   ...
   if(Field1=0,Null(),Field1) as Field1,
   ...

If you need to use that field in a horizontal calculation, you can use the Alt function:

   Alt(Field1, 0) + Alt(Field2, 0)

Another type field that we are often told to look at getting rid of is key fields.  They are often highly unique so will take up the most space in the dataset.  It is often straightforward to remove them, but what if they are needed?  Perhaps as a key link in a 1:M relationship.

For example, say you had an Account table with a key field called AccountID.  There is also an AccountHistory table which associates on AccountID.  You can't readily join the two tables so you need to keep the key field.  However, if there is not an entry for every Account in the AccountHistory table, you don't need to keep the value for that particular AccountID in the field!  Null it!

I might create a mapping table:

   KeyMap:
   Mapping Load Distinct
      AccountID, AccountID
   Resident AccountHistory;

Then, in my Account table load:

   ...
   ApplyMap('KeyMap', AccountID, Null()) As AccountID,
   ...

Now, this is not going to be significant over a million records.  However, over a hundred million, you will see a difference.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

The Right Background

I have talked about color choice recently, but whatever choice you make for the colors you are going to use, the choice of background color is critical.

You may have seen an image like this before:

If you haven't, the question is - which of the 5 squares inside the colored box is the same color as the square outside the box.  The answer is that all of the 5 squares are the same color.  The only difference is the background.

The rule, therefore, is that when choosing a background, it must be a single color.  You can't use any type of "gradient" effect or, even worse, use an image as your background.

So, what is the best color to use for the background?

In the world of software development, most IDEs (Integrated Developments Environments), such as Microsoft Visual Studio, have a default of a white background for the code and darker contrasting text.  There have been many debates on the use of white as a background.  The main one being that the white is wearing on the eye.

Some prefer black or dark blue with lighter contrasting text.  However, there is a visual effect that works against this - well known by photography experts - called Halation.  This is the effect of brighter colors seeming to spread when on darker backgrounds and hence look a little blurred.  This can cause eye strain.

Some suggest that softer grey backgrounds.  However, the issue here is that the softer contrast can have an effect on interpretation of color coding in scripting, and it is difficult to find a right choice of usable colors to offset this.  This is a feature that increases productivity in developers, so any reduction in its efficacy is not a welcome.

So, it appears that white might just be the right background color for developers, but this post is not about the best color to use for developing - it is for use in the display of quantitative data.

In an article called "Choosing Colors for Data Visualization", Maureen Stone, of StoneSoup Consulting and formerly of the Xerox Palo Alto Research Center, tells us that most color palettes are designed to be printed on white paper so using a white background is the right choice in digital form.  If your presentation is going to end up on paper, then designing on a white background is the right choice.

Further, Maureen tells us that there are perceptual advantages in using white as the human visual system is designed to adapt its color perception relative to the local definition of white and giving a white background gives us a stable definition of "white".


As a last word on this subject, I looked at six of the worlds most popular websites:

All use a lighter background and darker foreground  (not necessarily black, blue/purple are popular) and, with the exception of YouTube (which has only changed recently), that color is white.  These companies have the resources to spend a lot of money on choosing the right background colors.

Who am I to disagree with them.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner