Adding Maps to your Qlik Sense documents using Free Geo data
by Chris Casby
We all work in IT. Or in BI with BOs (Business Intelligence and Business Objects, respectively). We all report to a CTO who generates ROI justification reports for your much needed UI/UX requests and submits them to over-worked/over-stressed/over-clocked CFOs who have yet to approve your T&Es from last month. All the while, we, as willowy wanderers weave our way, without wane, through the worldwide web of acronyms and entitled titles to secure our spot on the most sacred of script: the payroll.
But aside from our never ending aspirations of ascension and acquisition of accolades and admiration amongst our peers and superiors alike, we find a tiny ray of light: the quest for knowledge, to know what to know for the sake of knowing. To become a more well–versed version of ourselves compared to yesterday’s model. And maybe apply it.
My apologies. Let me introduce myself. My name is Chris Casby. But most of my friends call me Casby. I’ve found that most people I have encountered in my life know anywhere between six and 500 different guys or girls named Chris, so I learned in high school that Casby was probably just easier if I wanted to be remembered. I can get into my hobbies and favorite authors and all that but really, that’s not why you are here.
I began my Qlik quest about two years ago. At that time, I had found myself stagnating in a soul sucking job in financial corporate America. It paid the bills, but not in any way that I was particularly proud of. So I stepped out of my comfort zone to explore the world of business objects and data visualizations, hoping against hope that I’d possibly be able to apply some of the little knowledge I’d acquired in my 5 years in college and my BS BS in Mass Communications with a concentration on Creative Advertising. And one thing that I can say with 100% confidence is that 100% confidence is rarely enough to get you by in the tech world. It may get you in the door, but at some point, people actually expect you to know what you are talking about, which was a novel concept for me, having worked for a credit card company for just shy of 4 years.
But in the past two years I’ve learned a few things, mostly from people who know more than me. Not least of which is that there are lots of people in the world that know more than me. And many of them just want to help people to know more. These are people who have invested their blood, sweat and tears into acquiring wells of knowledge, and are offering those wells to anyone willing to accept them. As I continue to learn, I’ll hopefully impart some of what I’ve learned to someone who is greener than myself.
So when I was first tasked with building a map chart for a dashboard, I had no idea what to do. But I knew exactly where to turn: Google!! However, I quickly realized that the Qlik Community was where I should have clicked right off the bat, as the first twenty links Google produced contained ten or fifteen links to the Qlik Community.
I was tasked with mapping Vendor information. In this scenario, my client used Qlik Sense as their Qlik platform of choice. I had access to the vendor addresses, which included their zip codes. But I found that I needed the latitude and longitude to utilize the Map chart. Latitude and Longitude information was not part of my data set so I was off to find it! After researching several options, I landed on the free database offered by GeoNames. Their geographical database contains latitude and longitude information for all countries and is available for download for FREE! Bonus that the data appears to be refreshed daily. The data I used can be found here.
Because I had access to the Vendors’ addresses and zip codes, I knew that I wanted to link the zip code with the latitude and longitude information in my data model. (This required a bit of clean up in the vendors table, but I will go into that in a different post.)
Here is how my table appears in the Table viewer. You can see what fields I loaded from the source data. There is more information available, but I only needed these fields for my task, so it’s best practice to avoid loading superfluous data as it may slow down processes.
Now that we had the latitude and longitude information for the client sites, we could use the GeoMakePoint function to generate coordinates. This is how it looked in my script.
Once we have these coordinates, we use that as the dimension in our Map object.
Here we are selecting the Map object from our different Chart options.
In the image of our script above, you see that we used the GeoMakePoint function to group the latitude and longitude into one value and named it Location. So in the image below, we are using it as our dimension.
Once we’ve selected our dimension we are prompted to choose a field as the identifier for the points. This image is that prompt and we are selecting place name, as that is how it was loaded from the database.
And this is the finished product! We put a text box along the top of the map with an expression showing us the number of records. This image has no filters selected, so we know that there are 594,584 records. And they are shown as points on the map. Along the right, we added a table of two list boxes to further filter the depiction of records on your map: One for country, and one for city.
As filters are selected, the number in the text box will decrease, as will the number of points on the map, representing your selections!
As we discussed earlier, there are a few ways to get a similar effect in the Qlik platforms. My hope is to contribute regularly to this blog and explore some of the different ways to make data visualizations more fun and readily available to everyone of all skill levels. And maybe help a few people along the way.
If you’re still here, thanks for reading! Feel free to comment below or email any thoughts to firstname.lastname@example.org. I hope I’ll be blogging again at you soon!