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:
- Qlikview demo application: GeoChartViewer.qvw
- Extension object as QAR file: GGeoChart.qar
- Extension object as ZIP file: GGeoChart.zip
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:
This comment has been removed by the author.
ReplyDeleteHow 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
ReplyDeleteHi,
ReplyDeleteYou 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,
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
ReplyDeleteHi,
ReplyDeleteIt 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,
Hi,
ReplyDeleteI 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?
Hi,
ReplyDeleteThe 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,
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.
ReplyDeleteHi,
ReplyDeleteHere 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,
Thank you! But I can't seem to get it to work. I do a load like this (just for tryout):
ReplyDeleteLOAD * 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?
I tried it with the sample data you provided above and it worked.
ReplyDeleteJust 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,
For other people with the same problem, I solved it by upgrading to IE9!
ReplyDeleteThanks a lot for the update.
DeleteBest regards,
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
ReplyDeleteUnfortunately, I do not know how to fix this problem.
ReplyDeleteWhy dosen't Texas light up in your example, there are store with sales in the data?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello,
ReplyDeleteIt 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,
Hi,
ReplyDeleteIt worked, Thanks Aydin
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
ReplyDeleteHi,
ReplyDeleteFirstly, 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,
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.
ReplyDeletefunction 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});
}
ReplyDeleteHi
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);
}
Error ?
ReplyDeleteWindows 2003 server
Line : 1
Char 1
Error : 'Qv' Undefined
Code: 0
URL : http://qlikview/QvAJAXZfc/QvsViewClient.aspx?public=only&name=Extensions/GGeoChart/script.js
please explain anyone.....
ReplyDeletehow to handle errors in qlikview?
Hi Bilge,
ReplyDeleteI’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
Great job!!! Congrats!
ReplyDeleteHey Aydin,
ReplyDeleteI 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?
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.
ReplyDeleteThanks in advance,
Regards!
Dai
This comment has been removed by a blog administrator.
ReplyDeleteHi
ReplyDeleteWill this work on zip code level?
Thanks
Satish
great one. how do one bring in additional measure to it?
ReplyDeleteany help?
DeleteI am not sure if it is possible to bring in additional measure
DeleteHello,
ReplyDeleteLet 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.
Hi Aydin
ReplyDeleteI 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?
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?
DeleteThat's nice and thanks for sharing code.
ReplyDeletedo you have newer version of extension showing how to create google charts usng data from Qlik Sense app?