Visualization

Using the Google Chart API inside Qlikview

17 Comments

I recently had a requirement to create a heat map of the US States and set about exploring if there was an easier way than creating a QV scatter plot mapped over an image.

I found the Google Chart API. With some help from the QlikCommunity forum, I got a fairly pleasing result.

The Google Chart API is provided free by Google. You pass in an http request with parameters that describe the data and desired layout, and a chart image is returned. I won’t cover the details of the API, it’s well documented at http://code.google.com/apis/chart/. Rather, I’ll share my experience integrating it with QV.

Here’s a screenshot of my results. The map will update as selections are made in the Sales chart. The generated map is not clickable. It’s just a static image.

A working QVW of the above may be found in the Qlikview Cookbook at:
http://robwunderlich.com/Download.html

The map is a Straight Table with a single expression. The expression is the http://… string used to generate the map. The representation for the expression is set to “Image”. Thanks to Tom on the forum for showing me this technique.

The OnAnySelect document event is used to trigger a “showMap()” macro that creates the variables needed for the http string. In a production application, you would want to be more selective and use field level events on the fields relevant to the chart.

In addition to chart layout parameters, the http string contains two parameters that describe the data.

  • chld= provides the list of states
  • chd= provides the data values for the states

The States and Values are associated by ordinal position in the respective lists. I could not find a way to keep the lists in sync by using QV expressions alone. The solution was to use the Sales chart as my “data source”. The macro walks the rows of the table to build two variables — the State codes and the Sales values. Here’s a snippet of the macro code:

set obj = ActiveDocument.GetSheetObject("CH01")
' Collect the locations
locations = ""
for RowIter = 1 to obj.GetRowCount-1
locations = locations & obj.GetCell(RowIter,0).Text
next
ActiveDocument.GetVariable("vValues").setContent values, false

Another area where VBScript was useful was in encoding the data values as specified by the API. I chose the “simple encoding” method. The Sales values are translated to relative values within a range of single characters defined by the API. The doc http://code.google.com/apis/chart/#simple provided a javascript encoding example which I converted to VBS.

The encoding algorithm requires that the maximum value of the dataset be known to properly spread the individual values across the relative range. To determine the maxvalue in the macro, I use the QV evaluate() function to “callback” to the QV expression language.

maxValue = ActiveDocument.evaluate(
“max(aggr(sum(Sales), State))” )

Producing a chart with the Google API does have some downsides. The user must be connected to the internet and the chart will render slower than a native QV chart. It also does not provide for making selections in the chart and tooltip values like a QV chart does. But I found it to be a simple solution to my requirement. I hope that someday the QV product will provide regional maps as chart types.

Update October 3, 2008: Alistair on the QlikCommunity forum has posted an example of calling the Google Chart API without using macros, which I find to be the preferred method:

http://www.qlikcommunity.com/575/?tx_mmforum_pi1%5Baction%5D=list_post&tx_mmforum_pi1%5Btid%5D=3763&tx_mmforum_pi1%5Bfid%5D=9

The next update to the Qlikview Cookbook will include the “macro-less” technique.

Share

17 thoughts on “Using the Google Chart API inside Qlikview”

  4. Hi,
    just a question…the links to the work of Alistair seesm to be broken…I’ve tried to search for it on QlikView forum, but to no avail :(…
    Have you got the updated link?

  6. Hello,

    mybe somebody able to support me with the following (I posted also on the QlikView Community)!

    I would like to map two different and seperate information: I have one table with production sites linked to respective coordinates and one table with suppliers linked to respective coordinates, too.

    I named the coordinates (latitudes / longitudes) as Lat-Prod and Long-Prod and for the Supplier Lat-Supl / Long-Supl. It can be the case that a production site has the same coordinates as a supplier site so I created two seperate tables.

    I spend many hours / days on it but I am not able to map both information (Supplier and production sites) in the map. I would “simply” like to select one or more production sites and some supplier sites and map those in google according to the linked coordinates.

    Can somebody support?

    Thanks a lot for pulling me out of this headache;-)!

    Dominik

  7. Hi Everyone,

    This is a very cool app (and ability), but I’m unable to get it running on my Access Point. I can run the rendered URL in IE on the server, but looking at the chart through the QV app on the Access Point just results in a blank chart object. Any ideas what might resolve this?

    Thanks,
    DJ

  9. Hi DJ,

    I just tested on my local QV9 SR6 server and no problem with both the IE Plugin and Ajax clients.

    You said you could reach the URL from IE on the server. Did you run the IE session with the same userid that is running the Qlikview service? Maybe that id is not authorized or configured correctly (eg proxy server setting) to access the internet.

    -Rob

  10. Hi Rob,

    Thanks for your quick response!

    I’ve verified that the userid for QVS has full admin rights, and verified that ports 80, 443, and 4747 are open in the firewall.

    Do you remember if there were any special settings you enabled when you set up your server?

    -DJ

  11. Hi DJ,

    No, I did no special setup on the server. I think you should try using IE to fetch the URL but running with the service userid. There are a couple of ways to do that:

    1. Log onto the machine using the service account.

    Or use runas. — Roughly
    1. Open a command prompt and enter
    runas /user:yourdomainyourserviceacount cmd,.exe

    That will open a second command window.
    2. In the new window enter:
    cd program filesinternet explorer
    iexplore.exe

    That should open an IE window using the service account. Try the URL from there.

    -Rob

  12. Hi Rob, you’ve taken BI at a different level all together. I downloaded the cookbook. But, Google maps app seems to crash on QV 7. Is it not compatible with QV 7? Will it be possible to replicate the same on this verion. Appreciate your help

  13. @GP,

    I don’t know if it can run on V7. All the cookbook examples were created in V8. I’m only supporting 8.5, 9 and 10. Any chance you can upgrade?

  14. Thanks Rob, fantastic implementation. I am however having a problem with the map being “contaminated” by unrelated data. Using the example on your cookbook, if one adds to your online table another column with for example Returns, then a State which has only returns but no sales will still appear slightly colored (light yellow), instead of white, like the other States with no sales or returns. Any ideas?

  16. Yes, it’s the non-macro version, from your latest cookbook. I am applying your example to a map of Spain, where I am based. The problem is that even though I include in chd only sum(sales), any state that has any other activity (refunds, for example) but zero sales will appear colored, albeit very light.

  17. My guess is that a state appearing with a zero value will be colored with the minimal color. Adding the new Revenue column causes the state to appear.

Comments are closed.