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.

0 Apr 2020

Qlik View

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.

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.

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

2. Add CountryName as dimension and click Next

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”

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))

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.