Modern business is largely data-driven, but this has opened up new challenges for forward-thinking organizations.Read More
Is your data on the map? Manual Qlik Sense associations between SQL and KML map data
Your problem: You’ve got some map polygons in KML format and some data in SQL, how do you tie these together using Qlik Sense associations?
In a recent post talked about how useful associations are but they are not always made automatic. There’s not always going to be a perfect match so sometimes you need to do it manually. In this post I’m going to show how to do that with map data.
Requirements: I’ve attached the KML and data extract I’m going to use here. The KML will be added as a separate file but for the extract, you can choose to load it into an SQL database or directly into Qlik Sense as a CSV.
Note: I associate via the postcode region (only based on the first two letters of the postcode) and this assumes that it exists in both. It didn’t originally exist in the data extract but I used Kognitio’s geometry functions to add it. If this is of interest then let me know and I’ll do another blog on it!
What’s in the data?
In the KML there is a postcode region followed by a polygon (a series of latitude and longitudes).
The data extract is based on stores from our retail data in our getting started guide. This has the store ID, the latitude/longitude and postcode region.
Adding the data to Qlik Sense
Create a new app and open it and choose to add the data via files and other sources.
First let’s add the KML file, click on the button near the top to attach files to your app.
A new popup will open, find the KML file and click open. You’ll see a preview like this.
Click on Add data and you’ll be taken to the new sheet screen.
Now click Field and + Add where we’ll add in the extract data. I’m going to add data from a Kognitio source using ODBC but in theory, any other database should work here.
Check the box for your table and you’ll see a preview like before, once you’re satisfied click on load data and you’ll be taken to the data manager screen.
In the data manager, you’ll see both pieces of data we added as bubbles. You’ll also see there’s a recommended connection between them in the sidebar, click on it to see what it is suggesting.
It’s recommending the postcode region in both which is what we want, however, it hasn’t been applied yet. Why didn’t it apply it? Well you’ll see that this association is coloured orange which according to Qlik means:
Orange: the Data manager is fairly confident that these tables can be associated. For example, if two different fields have different labels, but contain single digit data, the Data manager will flag them as orange, because the data types are similar.
It’s confident but not entirely sure and so doesn’t apply it. This makes sense for single digit data e.g. binary columns which can easily be matched but you probably don’t want to match it automatically as it would likely match with a bunch of other binary columns. So it only makes assumptions and applies it when absolutely sure, quite smart.
In our case, there are probably postcode regions which only exist in the KML file and we don’t have the stores in those regions so it’s not a perfect match. So we’ll need to apply it ourselves manually which is easy enough. I initially thought it would associate them since they were so similar but learned the hard way that that’s not the case!
Click apply then load data and proceed to the sheet edit.
Creating the maps
Let’s make a polygon map first for our postcode regions. Drag a map chart from the left sidebar to the sheet.
Then click layers in the right sidebar and add an area layer. Add postcode-boundaries.area as the dimension and color it by postcode region and you’ll have something like this:
Now let’s make the point layer map for the individual stores. But first, we need to make a master field for the location using both the latitude and longitude. You could add them separately on the map but it’s useful to have as one if you’re using multiple maps.
Select master items > dimension > create new from the left sidebar and fill it in like so:
Where the Field is:
Then click add dimension and that’s the master field finished. Now drag another map chart to the sheet and choose to create a point layer map instead of an area one.
Add the location master field we just made, add some color if you wish and you should have:
Note there are about 20,000 stores and Qlik Sense won’t be able to display them all so you’ll see a subset of it instead.
Now you’ll notice these two maps are associated like most visuals in Qlik Sense. Selecting a region will zoom in on the stores in the other one and vice versa. If your lasso tools are up to par you can even lasso select the stores. A video of this is linked in the next section.
How can this be used?
With the ability to select specific stores or a whole region of them, you can apply this as a filter to other sheets in your dashboard. Or even better, use them as a filter for an ODAG (on demand app generation) and query/analyze specific pockets of your big data.
I used this map association and ODAG in this video where I colored by sales and allowed you to select the regions of most interest. For a step-by-step introductory guide to ODAG on Kognitio, look in our documentation.
Also check out this datasheet to see the benefits of using Kognitio as a data source for ODAG.