Spider Charts are a way to show a one to many relationship on a map. We do this all the time in Qlik. In the table you see, we have a number of very creative office names and Salesmen. Notice that Office A has 5 Salesman associated with it. Qlik Sense knows this and aranges the straight table so that the Office Name and the Salesman Name associated with it are the same row. Most of you now are thinking "why am I reading this" because we all take this association for granted all the time.
Well, If you look at the next image you'll see that I've added a map. Selecting a single office also makes the same associations. Spider Charts simply show that information in a different way. However, adding a map gives you spatial context you miss in a table. For example, in this fake data set we notice that all Salesman for this company work in an office outside of the city.
Drawing the associations on a map is one thing, but what if you could qualify the association? Not all relationships are equal and therefore spider chart lines should represent the differences. In the next image we've formatted the lines different colors to represent the type of relationship between the office and the salesman.
Perhaps the colors represent what type of product the Salesman sells. You'll also notice that the lines are dashed, yet another way to determine the type of relationship. In all, we can use 3 different expressions to change color, width and format to represent a spider chart relationship.
Spider charts prove a very efficient way to show relationships between points on a map. Let me know if you are using Spider Charts and, if so, how has it impacted your story on twitter @qvgorilla.
Wrap All Your Points in a Bin with the Magic of the Sense Editor
At QlikMaps, we’ve been hearing a lot more questions about Bins on a map. I’d like to spend some time discussing what we’ve learned, but let’s make sure we are all on the same page and know what Bins are.
The topic of Bins usually comes up shortly after somebody says something like;
“I need to map 150,000 points on map and the native Sense map can’t do it. Can QlikMaps?”
Well, that question comes with a lot potential answers but there’s only one right one; You are asking the wrong question. Too many points on a map break the insight possible through the visualization. Look at the example below.
The map shows 500,000 bridges in the US but you only see a small portion of them. Why? At this Zoom level points sit on top of points, hiding many locations under the surface. This problem disappears as you zoom, but how do you know what areas are worth exploring? The deluge of points is simply too much information for the visualization. Fortunately, there is a better way.
Binning is a technique the telecom has been using for a long time. The idea is to roll up many points to an area and color code the area based on the density of points it represents. The source points are not lost, but hidden behind the Bins until a zoom level is reached where individual points are distinguishable.
Take a look at the image below.
Using the same 500,000 data points, I have created square Bins that better represent the points. In this map, no shapes overlap and all points have representation in the visualization by shaping the color of each square; a higher density of points equals a darker color. This makes identifying high density areas that need to be drilled into much simpler to identify.
So, how do you make Bins? In the past, we at QlikMaps would suggest you use and outside tool like we did in this video:
https://vimeo.com/158645971
Now though, the development team from QlikMaps has made the process much simpler. It turns out that creating square bins is a simple matter of math that can be done in the Qlik Sense Script Editor. I’ve included the script below but a .qvf is also available on my Google Drive folder.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
/*
This script takes a table with lat and long fields and
1. Builds a WKT string that creates square bins over all areas where a lat/long is located
2. Identifies which lat/long points fall inside the created square bins.
Why would you do this?
When working with a large number of points, it is often difficult to pull meaningful
analysis from a map due to overlapping points. Binning allows you to aggregate points
into geographical areas based on proximity instead of geographical territories (States,
Zips, Province, etc.).
For a video showing how binning can benefit mapping needs, see QlikMaps in action at
https://vimeo.com/157730654
How do I use this script?
1. Place the load script for the data source containing your lat and longs in the User
Data folder. An inline table has been placed there as an example.
2. Update the vDimTable, vDimID, vDimLat and vDimLng variables on the User Variables tab.
3. Review the vLatInc and vLngInc variabls on the User Variables tab.
Place your load script below. The Inline table
below just serves an example.
//////////////////////////////////////////////
*/
Salesman:
LOAD * INLINE [
SalesmanName, SalesmanLat, SalesmanLng, Zip, County
Andrea Hayes, 32.32215129, -95.28064553, 75701, SMITH
Anne Burke, 32.19426342, -95.40404025, 75762, SMITH
Cheryl Shaw, 32.30869378, -95.38088927, 75709, SMITH
Denise Williamson, 32.34085688, -95.31485059, 75701, SMITH
Douglas Davis, 32.32629003, -95.17029457, 75707, SMITH
Emily Watson, 32.44102106, -95.31201215, 75706, SMITH
Evelyn Spencer, 32.19883143, -95.43494675, 75762, SMITH
Harold Bowman, 32.29225023, -95.1914807, 75707, SMITH
Jason Williams, 32.23493317, -95.33442338, 75703, SMITH
Jerry Cook, 32.24311323, -95.31968725, 75703, SMITH
Jimmy Washington, 32.29360826, -95.16187958, 75707, SMITH
Johnny Lawrence, 32.21145182, -95.23770333, 75791, SMITH
Julia Coleman, 32.36870552, -95.31960172, 75702, SMITH
Lawrence Hart, 32.1168258, -95.35333511, 75757, SMITH
Lawrence Ramirez, 32.32289032, -95.39034168, 75709, SMITH
Louis Morris, 32.42278599, -95.45248748, 75704, SMITH
Michael Miller, 32.23873407, -95.33297743, 75703, SMITH
Norma Crawford, 32.32864232, -95.31006406, 75701, SMITH
Roger Webb, 32.39243832, -95.23533429, 75708, SMITH
Roy Willis, 32.39216682, -95.19966536, 75708, SMITH
Ruth Powell, 32.13585839, -95.34402054, 75757, SMITH
Sarah Young, 32.48159757, -95.28838903, 75706, SMITH
Teresa Gonzalez, 32.32054783, -95.37549644, 75709, SMITH
Willie Jackson, 32.43941297, -95.22809598, 75708, SMITH
];
Variables below set provide information about
your lat/long table. Complete the information
below. Then, paste the load script for your
lat/long table in the User Data tab.
//////////////////////////////////////////////
*/
Set vDimTable = 'Salesman'; // Name of the table with your Lat/Longs
Set vDimID = 'SalesmanName'; // Name of the identifier in your Lat/Long table
Set vDimLat = 'SalesmanLat'; // Lattitude field in Lat/Long table
Set vDimLng = 'SalesmanLng'; // Longitude field in Lat/Long table
/*
//////////////////////////////////////////////
Variables below set the size of the Bins.
Standard size of .3 for both vLatInc and
vLngInc are average sizes for the US. Larger
numbers can be used to create larger squares
suitable for larger areas. Smaller numbers
can be used to create smaller squares suitable
for smaller areas.
Clean up User Data table and move to variable
based names to make script more portable.
//////////////////////////////////////////////
*/
NoConcatenate
coord:
Load
$(vDimID),
Floor($(vDimLat), $(vLatInc)) as binLat, // flooring lat to set vertical height of bin
Floor($(vDimLng), $(vLngInc)) as binLng, // flooring long to set horizontal width of bin
Floor($(vDimLat), $(vLatInc))
& '|' &
Floor($(vDimLng), $(vLngInc)) as binId // provide a unique ID for the bin
Resident $(vDimTable);
Drop Table $(vDimTable); // source table is no longer necessary
Sets the range for which to build bins. By
default the range is based on the lat/longs
provided from the tabel under the User Data
tab.
If a different range is required, you may
substitute in a lat and long in the max and
min in the coordRange table. Do not do this
unless you know exactly what you are doing.
//////////////////////////////////////////////
*/
coordRange:
Load
Min(binLat) as minLat, // southwest lat
Min(binLng) as minLng, // southwest lng
Max(binLat)+$(vLatInc) as maxLat, // northeast lat
Max(binLng)+$(vLngInc) as maxLng // northeast lng
Resident coord;
/*
// Variables below pull from the coordRange table. //
// Do not change variables, but instead replace //
// coordRange table if different range is required. //
*/
Let vSeedLat = Peek('minLat');
Let vSeedLng = Peek('minLng');
Let vLatRange = Peek('maxLat') - Peek('minLat');
Let vLngRange = Peek('maxLng') - Peek('minLng');
Let vLatRangeCount = $(vLatRange) / $(vLatInc);
Let vLngRangeCount = $(vLngRange) / $(vLngInc);
It’s been a while since I’ve posted here but Qonnections 2016 has ‘set the fire a flame’ again. Hopefully this post will be the beginning of the regular updating of this blog.
At Qonnections, I had the opportunity to present one of the technical sessions. For those of you reading this that were there, thanks. If you weren’t, we covered a number of QlikMaps (qlikmaps.com) related topics. I also had the chance to talk to a number of people at the Analytics8 booth and noticed that there was a great deal of interest in mapping.
That said, if you’ve never done anything with maps, the topic can be a bit overwhelming at first. I remember the first time I saw a map I was puzzled by how the shapes on the map were made. We’ve come to accept that the visualizations at our disposal come pre-installed with our Qlik Installation. I don’t have to build squares for bar charts or circles for a pie chart; the Qlik Engine takes the numbers I give it and displays them in a square or circle format. Not so with a map.
Each shape (State or Province) has to be defined in the data model before it can be formatted (color or opacity) using an expression. Defining shapes that go on a map takes a mental leap that most never make until you start working with a map.
Shapes can be stored in a number of different formats, similar to how data can be stored in SQL or a .QVD. Each format has its advantages and disadvantages and therefore each method of displaying shapes on a map in a Qlik Environment differ slightly depending on the need. In Sense, native mapping uses .KML files. KML files (Keyhole Markup Language) can be found through a number of pay providers or on the web. Please be careful though when downloading KML files on the web. Not all shapes are created equal and the cost to license the use of certain KML files can be expensive.
QlikMaps uses a compressed format that fits nicely in Qlik’s In-Memory environment and is stored in a standard .QVD file format, allowing optimized loads of large territories. These files cannot easily be found by searching the web but can be downloaded from the QlikMaps download page.
Suffice to say, when looking at adding a map, some thought has to go in to what you need to show on the map and where you are going to get the shape files from. It’s no longer possible to just take for granted that Qlik is giving you everything you need to build a visualization. Spend some time doing some research on the different formats and technologies out there and invite someone with a bit of expertise on mapping to help get you started. It’s not hard, but it is a different thought process that requires a bit of planning.
