How to Plot a Map in QlikView

By Tony Sui, Mon 14 November 2016, in category Business intelligence

maps, qlikview

  
@

Visualising your data on a map can be very insightful and interesting. This post will walk you through how to plot your geolocational data on a map in QlikView.

Generating Longitude and Latitude - Google Geocoding

In the script:

/* 1. In your original address table, create a new field called FullAddress, which is the address that will be passed onto Google Geocoding API. */
AddressTable:
LOAD
    City,
    State,
    Country,
    City & ', ' & State & ', ' & Country AS FullAddress
From [..\AddressTable];

/* 2. Create a Temp_Address table to hold the unique values in FullAddress. */
NoConcatenate
Temp_Address:
LOAD Distinct
    FullAddress 
Resident AddressTable;

/* 3. Loop through each value in FullAddress and pass it onto Google Geocoding API, which returns the Latitude and Longitude. */
gmap_key = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA       //Enter your Google API Key

let noRows = NoOfRows('Temp_Address')-1;

for i=0 to $(noRows)
    let address = peek('FullAddress',$(i),'Temp_Address');
    Geocoding: 
    LOAD
        '$(address)' as FullAddress,
        [result/geometry/location/lat] AS Latitude,
        [result/geometry/location/lng] AS Longitude    
    From
    [https://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&key=$(gmap_key)] (XmlSimple, Table is [GeocodeResponse]);

    NEXT

    Drop Table Temp_Address;

Now the latitudes and longitudes are stored in the Geocoding table

Note that for users of the standard API, there is a limit of 2,500 free requests per day

Plotting Maps in QlikView – The Google Static Maps API

In the script:

/* Generate a set of variables and tables, to be used in the map plotting in the front end */
gmap_key=AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         //Enter your Google API Key
max_zoom_level = 17; //maximum value can be 21, which zooms to streets and buildings 
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)';
map_size_x = '640';
map_size_y = '400';

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 
    ];

set hideprefix = 'maptype';

On the dashboard:

  1. Create a scatter plot.

    • uncheck the “Show Title in Chart” box
  2. In the Dimension Tab, add “FullAddress”.

    • uncheck the “Show All Values” box
    • uncheck the “Show Legend” box
  3. In the Expression Tab, go to the “Advanced Mode”

    • first, add “Longitude”, the expression is: =sum(round (256pow(2,($(var_zoom)-1)))+( Longitude ((256*pow(2,$(var_zoom)))/360)))
      • in the Background Colour settings, enter the following in the definition: =argb(150,52,152,219)
    • second, add “Latitude”, the expression is: =sum(((256pow(2,($(var_zoom)-1)))+((0.5log((1+(sin((Latitude)pi()/180)))/(1-(sin((Latitude)pi()/180)))))((-256pow(2,$(var_zoom)))/(2*pi())))))
    • (optional) third, add sum of sales, which will control the size of the dots.
  4. In the Style Tab, in the Look section, choose the third one on the right column. image_image1.png

  5. In the Presentation Tab, in the Representation section

    • uncheck the “Autosize Symbols” box
    • the value in the “Max Bubble Size” will control the dot size on the map
  6. In the Axes Tab: image_image2.png

    • X Axis Static Min: =(256pow(2,($(var_zoom)-1)))+( var_mid_long ((256*pow(2,$(var_zoom)))/360)) -round(map_size_x/2)
    • X Axis Static Max: =((256pow(2,($(var_zoom)-1)))+( var_mid_long ((256*pow(2,$(var_zoom)))/360)) + round(map_size_x/2))
    • Y Axis Static Min: =((256pow(2,($(var_zoom)-1)))+((0.5log((1+(sin(var_mid_latpi()/180)))/(1-(sin(var_mid_latpi()/180)))))((-256pow(2,$(var_zoom)))/(2*pi())))+round(map_size_y/2))
    • Y Axis Static Max: =((256pow(2,($(var_zoom)-1)))+((0.5log((1+(sin(var_mid_latpi()/180)))/(1-(sin(var_mid_latpi()/180)))))((-256pow(2,$(var_zoom)))/(2*pi())))-round(map_size_y/2))
  7. In the Colors Tab, in the Frame Background Section, choose “Dynamic Image”.

    • the expression is: ='https://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 &'&key='&gmap_key & '&.jpg'

Essentially, you will have a scatter plot, with a dynamic map in the background, which responds to your zooms on your data.