Google maps in QlikView

How you could embed Google Maps in your QlikView application? There are several extensions available to do this fast and easy.

Hey guys, today I will show you how you could embed Google Maps in your QlikView application. There are several extensions available to do this fast and easy.

Google Map in QlikView

I will do it all with the functionality of QlikView 12. This is a step by step guide. Soon we will make a video for this.

  • You need a raw data for county locations (latitude and longitude). In this example we will use data only for country, not detailed data for cities, streets and etc. Google provides this information with Country.csv file. You could review it from here: https://developers.google.com/public-data/docs/canonical/countries_csv. We have information for county code, longitude, latitude and country name. This is enough for this example.
  • We need to download this Country.csv or to read it directly from the webpage. This is a common information, which we will update rarely, so I prefer to create a .qvd file. Use the following script:

set vQVDPath = ‘YOURQVDPATH’; //end with \

CountryExtract:
LOAD country as Country,
latitude as Latitude,
longitude as Longitude,
name as CountryName
FROM
[https://developers.google.com/public-data/docs/canonical/countries_csv]
(html, utf8, embedded labels, table is @1);

store CountryExtract into $(vQVDPath)Country.qvd (qvd);

  •  You could use the County.qvd in your application, instead of during every reload to extract the information from the website. When you have the Country.qvd, you could comment this script, please leave only  vQVDPath. You could need it in the future, but not in the daily reload.
  • You need sales data, something to analyse. When you load your Country.qvd we will include data for sales. Please use the following script:

CountrySales:
Load *,
num(0.22 * Sales, ‘####0.00’) as Profit,
num(0.78 * Sales, ‘####0.00’) as Cost;

LOAD Country,
Latitude,
Longitude,
CountryName,
num(rand() * 1000000, ‘####0.00’) as Sales
FROM
[$(vQvdPath)Country.qvd]
(qvd);

  • Now you have one table with master information for the Countries and metrics for Sales, Profit and Cost. This is enough for the example.
  • Please add new tab in the script with the following Google Maps code:

// Google Maps Key
// get a key here http://code.google.com/apis/maps/signup.html
gmap_key = ‘xx’; // enter here your Google map key
max_zoom_level = 3; // How deep you want to drill down in the map? Maximum value 17

// Variables required for calculating map
// No need to change these
var_pi180= ‘=pi()/180’;
var_lat_offset= ‘0’;
var_mc2= ‘=256*pow(2,$(var_zoom))’;
var_mc1= ‘=256*pow(2,($(var_zoom)-1))’;
var_mid_lat= ‘=min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)’;
var_mid_long= ‘=min(longitude)+(max(longitude)-min(longitude))/2’;
var_zoom= ‘=max(aggr(if(max( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) )-min( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) ) <map_size_x AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))’;
var_maptype= ‘=if(isnull(only(maptype)),fieldvalue( ‘&chr(39)&’maptype’&chr(39)&’, 1 ),maptype)’; // typemap could be roadmap, mobile, satellite, terrain, hybrid
map_size_x= ‘600’;
map_size_y= ‘300’;
map_scale= ‘2’;

SET HidePrefix=’_’ ;
// Field required for calcualting best zoom level
_zoom_level:
Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);

maptype:
LOAD * INLINE [
maptype
roadmap
mobile
satellite
terrain
hybrid
];

  • Everything in the script is ready. Now lets reload it.
  • After reload we are ready to create the object. You should use scatter chart.
Scatter chart with QlikView

1. Click Add new object and choose Scatter chart and click Next

2. Add CountryName as dimension and click Next

Qlik Country name

3. Click on Advanced Mode

Add 4 Expressions:

  • Longitude: =sum(round(256*pow(2,($(var_zoom)-1)))+(Longitude *((256*pow(2,$(var_zoom)))/360)))
  • Latitude: =sum(((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))))
  • Sales: sum(Sales)
  • Pop-up: =’Country: ‘ & CountryName & chr(10) & ‘Sales: ‘ & num(Sum(Sales), ‘$#,##0.00;($#,##0.00)’)

For Pop-up please select “Text as Pop-up”

Expression in Scatter chart QlikView

Click next.

4.  On Sort Window, don’t change anything, just click next

5. Choose your Style and click next

6. On presentation tab click Next

7. On Axes tab:

X Axis

  • Select Hide axis
  • Static Min: =(256*pow(2,($(var_zoom)-1)))+( var_mid_long  *((256*pow(2,$(var_zoom)))/360)) -round(map_size_x/2)
  • Static Max: =((256*pow(2,($(var_zoom)-1)))+( var_mid_long  *((256*pow(2,$(var_zoom)))/360)) + round(map_size_x/2))

Y Axis

  • Select Hide axis
  • Static Min: =((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin(var_mid_lat*pi()/180)))/(1-(sin(var_mid_lat*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))+round(map_size_y/2))
  • Static Max: =((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin(var_mid_lat*pi()/180)))/(1-(sin(var_mid_lat*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))-round(map_size_y/2))
Qlikview scatter chart axes

Click Next.

8. Paste dynamic Image in tab Colors as background

In “Frame Background” select Dynamic Image and paste as expression:

=’http://maps.googleapis.com/maps/api/staticmap?center=’  &  num(var_mid_lat, ‘##############’, ‘.’, ‘,’ )   &  ‘,’  &  num(var_mid_long, ‘##############’, ‘.’, ‘,’ )   &  ‘&zoom=$(var_zoom)’  &  ‘&maptype=’&var_maptype  &  ‘&size=’&map_size_x&’x’&map_size_y  & ‘&sensor=false’ & ‘&scale=’&map_scale

Make sure that “Plot Area Only” is not selected.

QlikView colors

Click Finish now. You Should see something like this:

Let`s make this object better. Right click and select properties for the object.

  1. General tab – deselect “Show title in Chart”
  2. Presentation tab – deselect “Show Legend”
  3. Caption tab – deselect “Show Caption”. Select proper:   Width: 2X;   Height: X;
  4. Click “OK”

Now your object should look like this:

GoogleMapsInQlikView

This was our version of embedding Google Maps in QlikView. If you have any questions, please use our contact form.

Download Link For The QlikView File

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on whatsapp
WhatsApp