29 April 2012

Google GeoChart in Qlikview

I wanted to use Google GeoChart in Qlikview and built a Qlikview Extension Object which displays a heat map generated by Google GeoChart. Although it does not work perfectly, it may give an idea and may be a starting point for people who would like to use Google GeoChart in their Qlikview applications.  Below is a screenshot from my demo application:



My demo application and the extension object set-up files can be downloaded from the following links:

If you'd like to see it working on your computer, please:
  • Run GGeoChart.qar (i.e. double click on it) to install the GGeoChart extension object.
  • Or, extract the GGeoChart.zip file under Qlikview's extension objects folder. (In Windows 7, it is C:\Users\[UserName]\AppData\Local\QlikTech\QlikView\Extensions\Objects). 
  • Open the demo application GeoChartViewer.qvw with Qlikview and turn the webview mode "ON" from the view menu.
  • At first you should see a world map, then drill down to country level by selecting United States in countries list-box and then drill down to state/province level by selecting California in provinces list-box.

If you'd like to use the extension object in your qlikview application, please:
  • note that a variable is necessary to keep the drill down level (i.e country, state/province, city). By default, the variable is assumed to be named as vGeoLevel. The extension object is relying on this variable to find out the level. The value/formula of the variable can be copied from the GeoChartViewer.qvw application (pls. see the link above).

Facts, known issues, etc.:
  • The sales data in GeoChartViewer.qvw is from Microsoft's Contoso database.
  • The geographic data (i.e. country, state/province, city) in GeoChartViewer.qvw is from Google.
  • GeoChartViewer.qvw is tested on Qlikview 11 SR1.
  • GeoChartViewer.qvw is tested on a computer with Internet Explorer version 9. 
  • The map displayed by the extension object does not work well for every set of data. For example, if one checks United States data in the GeoChartViewer.qvw, (s)he should be able to drill down from World to USA, then USA to California level and finally see the data for the cities in California. However, if one selects Italy, then (s)he will not be able to see the data until (s)he selects a province. That is because Google GeoChart does not recognize the province names which are passed to it. 

For those who would like to get familiar with qlikview extension objects, I strongly suggest to read the  following blogs:



38 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. How I edit/ load other files with the data (sales) or add some new file for contry/county code ? e.g i want to add ISO-3166-2 only for Romania

    ReplyDelete
  3. Hi,

    You can build a new qlikview application with only ISO-3166-2 codes for Romania. You should use the same field names as in my sample qlikview application, i.e. Country, CountryCode, Province, ProvinceCode, Cityname. By default, expression in the GGeoChart extension is Sum(SalesAmount), so you can either name your fact field as SalesAmount or change the expression of the GGeoChart from the properties.

    Best Regards,

    ReplyDelete
  4. I used the same field names (Country, CountryCode, Province, ProvinceCode, except Cityname, yet)into another qlik file and it worked like a charm. I still want to improve the geochart (i like the ideea very much because the visual thing) but I am not very good to javascript (I looked into script.js on your extension but I was sincerly kind of lost ... Can you kindly explain the rows and what are used to so I cand edit step by step (please exscuse my lack of knowledge but I am an accountnant not a programer/IT stuff). Sincerly yours Daniel Robu

    ReplyDelete
  5. Hi,

    It looks like we may have to discuss and exchange ideas a few times.
    Could you pls. pass me your email address or send me an email (pls. see ABOUT ME on right hand side of this blog) so that I can have your email address?

    Regards,

    ReplyDelete
  6. Sandro Mendes14 June, 2012 15:36

    Hi,

    I try a script with the colums CountryCode, Country, StateCode, State for my country Brasil.
    For Example:

    LOAD * INLINE [
    CountryCode, Country, StateCode, State
    BR, BRASIL, BR-AC, Acre
    BR, BRASIL, BR-DF, Distrito Federal
    BR, BRASIL, BR-PE, Pernambuco
    BR, BRASIL, BR-RJ, Rio de Janeiro
    BR, BRASIL, BR-SP, São Paulo
    ];

    It doesn't work. When i select the State name nothing change in map. Note: I used the ISO 3166-2. http://pt.wikipedia.org/wiki/ISO_3166-2:BR

    I've another doubt. How to set a color in the state/province?
    Can you help me?

    ReplyDelete
  7. Hi,

    The problem might be the field names, I suggest you to try changing StateCode to ProvinceCode and State to Province. I tried and it worked, if you pass me your email address or send me an email (pls. see ABOUT ME on right hand side of this blog) , I can send it to you.

    Regarding color, it is hard-coded in the script.js file. You can look for 'color' keyword in the script.js file and change the hexadecimal color codes to have different colors.

    Regards,

    ReplyDelete
  8. Could you please provide the value/formula of the variable vGeoLevel? Since i'm on an educational qlikview licence and can't open the example file. Thanks.

    ReplyDelete
  9. Hi,

    Here is the the formula of vGeoLevel:

    =
    if(isnull(only(Country))=0 and isnull(only(Province))=0 and isnull(only(CityName))=0,'CityName;' & if(only(CountryCode)='US',only(ProvinceCode),only(CountryCode)),
    if(isnull(only(Country))=0 and isnull(only(Province))=0 and isnull(only(CityName))=-1,'CityName;' & if(only(CountryCode)='US',only(ProvinceCode),only(CountryCode)),
    if(isnull(only(Country))=0 and isnull(only(Province))=-1 and isnull(only(CityName))=-1,'Province;' & only(CountryCode),
    if(isnull(only(Country))=-1 and isnull(only(Province))=-1 and isnull(only(CityName))=-1,'Country;World',''
    ))))

    Pls. let me know any questions.

    Best regards,

    ReplyDelete
  10. Thank you! But I can't seem to get it to work. I do a load like this (just for tryout):

    LOAD * INLINE [
    CountryCode, Country, ProvinceCode, Province, CityName, SalesAmount
    US, United States, US-AL, Alabama, Abbeville, 1000
    US, United States, US-AK, Alaska, Anchorage, 2000
    BR, Brasil, BR-AC, Acre, Rio Branco, 4000
    ];

    and I set the variable vGeoLevel to your expression, I checked this in a text box, it works.

    The problem is I don't get any image whatsoever i the extension object, it is just blank. No matter what?

    ReplyDelete
  11. I tried it with the sample data you provided above and it worked.
    Just to make sure, did you turn ON the WEBVIEW mode?.

    Please send me an email (you can find my email address in "ABOUT ME") if you would like to discuss further?

    Best regards,

    ReplyDelete
  12. For other people with the same problem, I solved it by upgrading to IE9!

    ReplyDelete
    Replies
    1. Thanks a lot for the update.

      Best regards,

      Delete
  13. hi, i was trying to view this extension on a server,it works well with Full Browser Version but it doesn´t work with Internet Explorer Plugin, in the extension appear a text like "Unknown Chart Type", i already put the extensions documents in "% ProgramData% \ QlikTech \ QlikViewServer \ Extensions \ Objects.", do you known what is the problem i need to see the extension with Internet Explorer Plugin

    ReplyDelete
  14. Unfortunately, I do not know how to fix this problem.

    ReplyDelete
  15. Why dosen't Texas light up in your example, there are store with sales in the data?

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. Hello,

    It may be because of the default data limit in extension objects. The default value can be overwritten in the "definition.xml" file of the extension object. (In Windows 7, it is under the "C:\Users\[UserName]\AppData\Local\QlikTech\QlikView\Extensions\Objects\GGeoChart" folder).

    You can try to change it to 1000 in the "ExtensionObject" tag in definition.xml as follows:

    [ExtensionObject Label="Google GeoChart" Description="Display Data on Google GeoChart" PageHeight="1000"]

    Please replace the [] with <> in the above tag expression.


    Please let us know the result.

    Thanks and best regards,

    ReplyDelete
  18. Hi,

    It worked, Thanks Aydin

    ReplyDelete
  19. This is really useful, however I've managed to get the ISO codes for the regional breakdown in the UK (to County) level, i.e, GB-AGB Argyll & Bute, and was trying to bring those in which apparently Google Charts are meant to support, but although I've tried adding in another level to the java script and created a new 'RegionCode' which it references both in the Google Chart and also in the app and associated it all within QlikView, it doesn't appear to be working, any ideas? I'm happy to share the app and data I have. I would like to show these as a further breakdown in the extension object. Also, the extension object does not appear to be clickable and update QlikView data is it meant to? Cheers, Julie

    ReplyDelete
  20. Hi,

    Firstly, yes this extension object is not clickable.

    If you haven't tried already, I suggest you to try the ISO codes in google playground for geochart:
    https://code.google.com/apis/ajax/playground/?type=visualization#geo_chart
    If the ISO codes are working fine there, then please send me the app and the data, I can have a look when I have time.

    Regards,

    ReplyDelete
  21. Hiya, thanks for your help, I tried to input the basic high level code of GB-ENG, GB-SCT etc.. which wouldn't even work in the playground even though it works in your extension object. I just tried overwriting the data that was in the playground, but it wouldn't accept it even when I was using Country or Region as the label.

    function drawVisualization() {
    var data = google.visualization.arrayToDataTable([
    ['Country', 'Popularity'],
    ['Germany', 200],
    ['United States', 300],
    ['Brazil', 400],
    ['Canada', 500],
    ['France', 600],
    ['RU', 700]
    ]);

    var geochart = new google.visualization.GeoChart(
    document.getElementById('visualization'));
    geochart.draw(data, {width: 556, height: 347});
    }

    Code from playground - when replaced with GB, FR etc.. it worked, but the below didn't whether it was using Country or Region..

    function drawVisualization() {
    var data = google.visualization.arrayToDataTable([
    ['Region', 'Popularity'],
    ['GB-SCT', 200],
    ['GB-NIR', 300],
    ['GB-WLS', 400],
    ['GB-ENG', 500],
    ['France', 600],
    ['RU', 700]
    ]);

    var geochart = new google.visualization.GeoChart(
    document.getElementById('visualization'));
    geochart.draw(data, {width: 556, height: 347});
    }

    ReplyDelete

  22. Hi

    The code below give the regional breakdown for UK in google play ground.
    But if you like to have a breakdown for Scotland, I am not sure if this is supported by google geochart.

    function drawVisualization() {
    var data = google.visualization.arrayToDataTable([
    ['Country', 'Popularity'],
    ['GB-SCT', 200],
    ['GB-NIR', 300],
    ['GB-WLS', 400],
    ['GB-ENG', 500],
    ]);

    var options = {
    region: 'GB',
    displayMode: 'regions',
    resolution: 'provinces',
    colorAxis: {colors: ['green', 'blue']},
    width: 556,
    height: 347
    };

    var geochart = new google.visualization.GeoChart(document.getElementById('visualization'));
    geochart.draw(data, options);
    }

    ReplyDelete
  23. Error ?

    Windows 2003 server

    Line : 1
    Char 1
    Error : 'Qv' Undefined
    Code: 0

    URL : http://qlikview/QvAJAXZfc/QvsViewClient.aspx?public=only&name=Extensions/GGeoChart/script.js

    ReplyDelete
  24. please explain anyone.....

    how to handle errors in qlikview?

    ReplyDelete
  25. Hi Bilge,

    I’ve been sneeking around “Google maps within Qlikview” for a few hours thru demos, videos and others comments in community.
    So far, your blog is the most performant I got but I can’t explain why a map shows up or not on my machine.

    Your application shows the map. Some others don’t.
    But anyway I can’t access properties (web view mode).

    So can you tell me how I have to proceed to build a .qar or a .zip. I guess this is mandatory to define geographical zone I want to show?
    And what I should do to add a new object and see properties?

    Best regards,
    Chris


    ReplyDelete
  26. Great job!!! Congrats!

    ReplyDelete
  27. Hey Aydin,
    I used and modified your extension object and it works great! love the look and feel. However, I was wondering if i could add multiple measures into the chart. Could you help me out with that please?

    ReplyDelete
  28. Hi! have you managed to make it work for Italy? Regions are not working. Nevertheless when you select a province, it shows all the dots for cities.... maybe it's the fieldname? Instead of Region it may be referenced with a different name.
    Thanks in advance,
    Regards!

    Dai

    ReplyDelete
  29. This comment has been removed by a blog administrator.

    ReplyDelete
  30. Hi

    Will this work on zip code level?

    Thanks
    Satish

    ReplyDelete
  31. great one. how do one bring in additional measure to it?

    ReplyDelete
    Replies
    1. any help?

      Delete
    2. I am not sure if it is possible to bring in additional measure

      Delete
  32. Hello,
    Let suppose one want to add drill down functionality in Geo-chart. Like if one click on country another map opens with cities in that specific county. So, kindly guide me what is the best way to add drill down functionality.

    ReplyDelete
  33. Hi Aydin

    I would like to use the excel file which lists the city, provide, address and keys in a model of mine. I see for South Africa there are only about 4 or 5 GeographyKey's. Which are these? Are these Province, City, Address related? Where do I get them? Do I need to get the GeogpraghyKey for for each Province, City, Address, and where can I get these?

    ReplyDelete
    Replies
    1. Sorry Aydin; are these Key's used by Google or Key's to join your data sets, i.e.: City, Province etc. I just thought these were required by the Google API? Will Google GeoChart pick-up the Province Code and Address, and highlight the Province or Spot the address?

      Delete
  34. That's nice and thanks for sharing code.

    do you have newer version of extension showing how to create google charts usng data from Qlik Sense app?

    ReplyDelete