Create a KML polygon shape file manually and use it in Qlik Sense

    I spent some time working through this process for a project and thought I'd document my approach. These instructions were created using Qlik Sense Desktop v 2.0.1. but should be compatible with all versions of Qlik Sense as KML data is a commonly used source.


    For the following example I have used the best of the few websites that are freely available at the time of writing. There are others but some don’t provide a readable form of KML or miss certain tags in the KML that are needed. The site I use for this example is http://www.birdtheme.org/useful/v3tool.html

     

    I’d be delighted to hear if anyone has a better option. However, this site has served me well in creating a few examples and if you follow the steps you should get a decent result with minimal effort (unless you’re plotting a very complex set of polygons of course).

    I have attached all files that I used in creating this demo, as well as the final Qlik Sense .qvf file (no extensions needed). You can follow the steps and create it yourself or review the files.

     

     

    What is a KML Polygon shape file

    KML (keyhole markup language) allows us to create map overlays using geo coordinates (longitude, latitude) for set of many points which create a shape and a contained area. These files are used when showing countries on a world map or states on a map of the US but any shapes can be created using geo coordinates. These shapes can then be color coded, showing heat maps etc, and are fully selectable in Qlik Sense. This example will walk you through this process.

     

     

    Create the KML Polygon file

    1. Open Tool for Google Maps v3 (version 3) and navigate to the area I wish to create a KML File for… I’ve chosen Dubai Mall just for an example.

    1.1.png

    2. To create some polygon shapes on this map, ensure that polygon is selected from the first dropdown menu (see screenshot above).


    3. Click on [Next Shape] to activate the map for a polygon capture.


    4. As you click around the boundary of the shape a polygon will appear shaded in purple.

    1.4.png


    5. Once you have completed your shape (you can always [Delete Last Point] if you have clicked in error) click on [KML input] as we need to define a <name> for our polygon shape, in my example I will call this ‘Building 42’. Then click [Save].

    1.5.png


    6. Click on [Next Shape] to create the next shape following the same steps. For example I have created 7 shapes with names ‘Building 40’ through to ‘Building 46’.

    1.6.png

     

    7. When you have completed the last shape and saved the name change. Click on [Show KML] and copy the text that appears in the box.

     

    8. Open a notepad (or similar text editor) window, paste this code and save the file as a .xml file (please ensure that you do not save it as a .txt file by selecting all files as the save as type when saving). In my example I have saved the file as Dubai Mall.kml

     

     

    Create your Qlik Sense application

    1. Create a new application.

     

    2. Drag the [Sales Details (kml test).xlsx] file into your Qlik Sense application and load in the Sales Data dataset (worksheet). Note the [Store Name] field holds the same data as we have entered against the Polygon shapes. Load this data.

     

    3. Drag the [Dubai Mall.kml] file into your Qlik Sense application. The second field [Dubai Mall.Point] is blank so uncheck this field

    2.3.png

     

    4. Click on Profile to use the smart data load to compare the two data sources.

     

    5. It will show us that a field in each dataset contains 100% the same value set. [Store Name] and [Dubai Mall.Name]. Click on this recommendation and rename both fields to [Store Name]..

    2.5.png

     

    6. Load and finish

     

    7. The data model viewer will now show a model with a join as below.

    2.7.png

     

    8. The autogenerated script in the data load editor will look as below.

    2.8.png

     

     

    Use the KML file in a map object

     

    1. Create a new sheet in your Qlik Sense application.

     

    2. Click into Edit mode, and drag in a Map Chart object

     

    3. Drag the field [Store Name] into the map object on your screen and it should add your shapes immediately.

    3.3.png

     

    4. Drag in some other objects and when you filter on Store Name, or select on the KML shape you will see the application apply the filter.

     

    5. My first example below highlights how the colors can show the relationship between the shape and the Store Name. I’ll accept it is a little colorful.

    3.5.png

     

     

    Extend this example with conditional color formatting

     

    1. Create the following color effect on the KML map object

    4.0.png

     

    2. create a measure in the master objects using the formula:

    num((sum([Sales Amt TW])/sum([Sales Amt LW]))-1,'#,##0.0%')

    Setting the num() format mask in the measure makes life easier when using the measure in multiple objects.

     

    3. Drag this measure into your map and click on [Add “Sales Growth %”]

     

    4. On the right hand side properties, navigate to Appearance > Colors and legend. Uncheck the auto color selector and select 'by expression' in the dropdown

     

    5. Use the following formula to set a green and red color based on RGB values:

    if([Sales Growth %]>0,rgb(120,255,120), if([Sales Growth %]<0,rgb(255,120,120), rgb(220,220,220)))

     

    6. Now you have a dashboard which indicates how the stores are performing within the KML polygon shapes allowing you to see areas/ stored that are performing and those that are not.

     

     

    I hope this guide is useful, please leave some feedback if you have any ideas for improvement.

     

     

    Important note:

     

    In using the Google maps API to generate these shapefiles you should be aware that this may be against the terms of use that we have all agreed to with Google by simply using Google Maps. There are alternative mapping options such as Open Street Maps etc (which Qlik Sense makes use of) but I have yet to find a KML generator such as this using this a mapping source..