Select Page

As I mentioned in my last post we have loaded the KML into Qlik Sense but it’s not that useful as the Name field is missing and being populated with an automatically generated value p<rownumber>.

Qlik_KML_Load

Source: OptimalBI

If you open the KML file in a text editor you will see that for every object there is an appropriate field we can use for the Name. In my scenario when dealing with a map broken up by regions the Attribute is  REGC2016_N

<ExtendedData><SchemaData schemaUrl="#REGC2016_GV_Clipped">
 <SimpleData name="REGC2016">01</SimpleData>
 <SimpleData name="REGC2016_N">Northland Region</SimpleData>
 <SimpleData name="AREA_SQ_KM">12510.35034480000</SimpleData>
 <SimpleData name="LAND_SQ_KM">12497.92090900000</SimpleData>
 </SchemaData></ExtendedData>

 

So, to align the KML data with the Metadata nested inside the same file we need to extract it as XML and do some data manipulation to join the sets of data together.

First, load the KML file using the data editor. You should get something that looks like the following code in your editor.

LOAD
 REGC2016.Name,
 REGC2016.Point,
 REGC2016.Area
FROM [lib://AttachedFiles/REGC2016.kml]
(kml, Table is [REGC2016/REGC2016]);

 

Next, load the file again but this time as XML format.

Qlik_load_XML

Source: OptimalBI

If you browse through the tables available in the file you will find one that has your data in it. Uncheck all the other tables and insert the script.

Qlik_select_XML

Source: OptimalBI

This should add a script that looks similar to this

LOAD
 SimpleData%Table,
 %Key_Placemark_8783892621580015
FROM [lib://AttachedFiles/REGC2016.kml]
(XmlSimple, table is [kml/Document/Folder/Placemark/ExtendedData/SchemaData/SimpleData]);

The problem we have is that because all our data has the same XML Tag instead of getting 1 row for each object we get 4 rows.

 <SimpleData name="REGC2016">01</SimpleData>
 <SimpleData name="REGC2016_N">Northland Region</SimpleData>
 <SimpleData name="AREA_SQ_KM">12510.35034480000</SimpleData>
 <SimpleData name="LAND_SQ_KM">12497.92090900000</SimpleData>

My solution isn’t perfect, however, it should work as long as every object has all 4 attributes present. Thankfully, Stats NZ is well complete so we can use the approach I’ve used below. This code used the row position of the data in the file to match up the object to it’s associated metadata.

Change the KLM load script o the following

LOAD
 //REGC2016.Name as Council_Key,
 RecNo( ) as Council_Key,
 REGC2016.Point,
 REGC2016.Area
FROM [lib://AttachedFiles/REGC2016.kml]
(kml, Table is [REGC2016/REGC2016]);

This creates a key per object based on its position in the file.

Next change the XML load script to the following code

GENERIC LOAD
    div(RecNo( )-1,4)+1 as Council_Key,
    if(mod(RecNo( ),4)=1,'COUNCIL_CODE',
       if(mod(RecNo( ),4)=2,'COUNCIL_NAME',
          if(mod(RecNo( ),4)=3,'AREA',
            if(mod(RecNo( ),4)=0,'LAND_AREA',mod(RecNo( ),4) ) ) ) ) ,
    SimpleData%Table  as TEMP_ROWS
FROM [lib://AttachedFiles/REGC2016.kml]
(XmlSimple, table is [kml/Document/Folder/Placemark/ExtendedData/SchemaData/SimpleData]);

What we have done here is associate each group of four rows of metadata to the object row they belong to and pivot the data such that it is conceptually one row instead of four. We have now got a usable model for including in visualisations.

To test that is all works you can do the following:

  1. Create a new sheet and edit it
  2. Select the Chart type Map and drag and drop into the sheet
  3. Drag the field COUNCIL_NAME onto the map.
  4. Select Add as area layer
  5. Drag the field COUNCIL_NAME onto an empty part of the sheet
  6. Done
Qlik_working_map

Source: OptimalBI

You should now have a map that is responsive to clicks and will also highlight regions base on clicking on a Council Name.

All the code, all the fun – Ben

Ben writes blogs about the technical side of BI the code, all the code and not much other than the code. 

You can read all of Ben’s blog here.

Don’t forget, we can train your team in the art of agile business intelligence at any time!

%d bloggers like this: