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.
A client of mine kept their PostgreSQL database locked away behind an SSH connection. As in most situations, convincing the IT department to change security rules for one dashboard was not a hill I was willing to climb. Instead, I used some old techniques I’d used to connect to my home servers back when I had such a thing.
I use SSH rarely now so I make use of a Chrome extension called Secure Shell for the occasional SSH connection. For this blog post, I’m going to be using the Chrome extension, but the same technique will work with PuTTY or any other SSH tool.
Connecting via SSH
In the image above, a connection was established to the PostgreSQL database through the SSH client. The connection was then routed through a port on the local machine.
The username to connect via SSH to the PostgreSQL database.
The address of the PostgreSQL server.
-L is a flag used in Linux that tells the SSH client to forward the connection. Depending on the client you are using, the flag may not be necessary.
The local port being used by the data connection we will set up next.
The local address being used by the data connection we will set up next.
The open port on the PostgreSQL database that is listening over SSH.
Create an ODBC Connection
Once the SSH connection was set up, I created a System DSN that connected to the PostgreSQL database by routing through the local port. (The numbers below correspond to the numbers in the image above.)
Usage
Once the connection was set up, using it was relatively simple.
I connect to the database through through my SSH client.
I opened Qlik.
I Create a data connection using the ODBC connection.
Once the connection string was created selecting tables was the same as connecting to any other database. The only annoyance is having to connect via the SSH client before I reload.
As Sense becomes more popular, going from *.qvw to *.qvf files will likely become a more and more common occurrence. There is a feature built into Qlik Sense Desktop that allows you to easily convert the script, variables and data model of a .qvw into a .qvf. This will NOT import UI elements, but porting over everything else will greatly reduce the effort of moving to Sense.
Here’s how:
Open Qlik Sense Desktop.
Open a Windows Explorer window to the file path of the .qvw you want to convert.
Drag and drop the .qvw file you want to convert into the Qlik Sense Desktop Window.
Qlik Sense (2.x.x+) will create *.qvf in the directory along side the *.qvw file and open that file in a new Qlik Sense tab.
* Note there are some steps to take before attempting the conversion due to differences in features of the two products (see link below):
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.
Often we take QlikView’s associative data model for granted. Make a selection and QlikView reads your mind to make selections otherwise made invisible by a million rows of noise. Rarely do we take the time to understand what’s happening behind the scenes where we can bend QlikView’s associative model to our needs.
Let’s stop and think about QlikView’s method of selection. You pick a row, rows of data turn green. Did QlikView make a selection based on an OR assumption or an AND assumption? If you’re like me, you know the answer but you have never thought through the process.
The first demo a customer usually sees using QlikView asks them to select a fruit and QlikView selects a color. An Apple can be Green OR Red.
My family just went apple picking and I can tell you that apples are Green ORRed; but there are plenty of apples that are Green AND Red.
Luckily QlikView has introduced a method of making an AND selection. You start by building a special table in to your Data Model. The new table needs to consist of two rows; one that serves to tie the table in to the data model and the other that will be representative of the field that you want multiple rows to be compared using AND.
Customer Number is the common key field and Product Sub Group ANDMode is the field to be used to compare using AND. There are two important criteria for this to work.
1. The table that stores the AND-Mode field has to be in a table with only two fields, one of which is a key and the other which is the field that will be used in the AND-Mode list box.
2. The records in the ANDModeTable must be distinct.
Once built in to your model, It’s important to enable the function in the UI of the application.
I encourage you to try out this feature. It opens up a world of possibilities. For more detailed information you can check out the Technical Brief. You can get it here along with a sample application.
Recently I've run in to two situations where an extremely savvy end user wanted the ability to build very complex charts. The problem, this end user had no way to obtain an end user license. This restricted them to what they could do in AJAX.
Let's all be honest; working in QlikView's AJAX client is a nightmare. The more complicated the expression the higher your blood pressure gets. I wanted to make development simpler for the savvy while providing a way for them to share with those less interested in blurring the line between 'power user' and 'developer'.
In the example below I was working with an end user who knew how to write basic expressions; Sum(Sales), Count(Order_ID), etc. What they needed to do is be able to easily compare the sum of sales to the sum of sales last year. A seasoned developer would build two columns;
The second expression is not for the faint of heart. Furthermore, even if I could teach the savvy power user how to build this, it would be very difficult to teach the average QlikView end user.
My solution was to create a .txt file:
CalculationType, Expressions
Current_Selection, {$}
Total, {1}
Last_Year, {$<[Year]={$(#=Year(Today())-1)}>}
I chose to use a text file, though I see no reason an Excel file wouldn't work. In the text you see that I have two columns; CalculationType and Expressions. CalculationType is just a user friendly name for the expression. Once I reload the document, a table is created.
The Expression field will be saved in a variable and used in the presentation layer of the application. Once the .txt file has been loaded in to the application I created a variable
The Expression field will be saved in a variable and used in the presentation layerof the application. Once the .txt file has been loaded in to the application I created a variable
vExpression = Expressions
At this point all I need to do is add the variable vExpression into my expressions
and select the calculation Type I want to perform.
Another benefit to this solution is the ability to share. Give savvy end users access to the .txt file to add theirown logic. After a reload, the whole QlikView community at your office can benefit from the additional expressions.
"Minion, the new vendor that provides daily widget sales has decided to provide us all the data we need. They will make it available as a daily snapshot stored in 100 .csv files that are archived in a single .zip."
Never happened to you? I did to me...not the minion part...but you get the idea. The vendor provided a .zip file with many different .csv files that needed to be read in to a QlikView document. Since QlikView cannot read directly from an archived .zip file, I had to find a way to extract the files I needed prior to reading them in.
Extracting .zip files is easy. Automating on a schedule gets a little trickier. For this I chose to use 7zip, an archive utility that can manage many different types of archives. If your data is in a .rar, .tar, .gzip, etc. this same basic solution should work. In addition, 7zip does NOT HAVE to be installed. You can find standalone versions intended to be run from a usb drive. This is important for those developers who don't have Admin rights to their box and need an application that doesn't need to be installed to work.
7zip can be called from a command line.
Please note the '*' at the end of the command. It tells 7zip to put all extracted files in a folder with the same name as the archive. In this example, all the contents of 20120416 - 20120417.zip would be put in a folder named 20120416 - 20120417.
Also, the example above was copied from a Notepad file, hence the reason for all the double quotes. They are not necessary in the command unless you are specifying a file name with spaces.
That being said, the above command can be moved directly in to a .bat file and run using windows scheduler.
QlikView has loading data from an FTP site built directly in to it's product. I recently ran in to a scenario that required a connection to a Secure FTP site. As of QV11, connection to an SFTP sites is not supported.
To handle this I used PuTTY, a free telnet/SSH client, to pull the file down to a local drive where QV could read it. One of the great things about PuTTY is that it does not HAVE to be installed. You can download a portable edition that just sits on a folder on your desktop. This is important because many QV developers don't have administrative rights on their box making installation a no go.
I'm not a PuTTY expert so I welcome any feedback. In the meantime, here's how I was able to get things up and running.
PuTTY is a command line tool that expects user interaction to perform a function.
If you type in
You get
here I would type something like
This tells PuTTY to get the first file (20120410 - 20120411.zip), download it, and save it to "C:\20120410 - 20120411.zip".
The fact that PuTTY is interactive and asks me for direction at every step causes an issue. I need to be able to automate the script. The answer is to point PuTTY to another batch script with the commands I want it to perform. If you use the -b switch with PuTTY, it will execute the .bat file you point it to. For example
All I've done is to take the initial command that opened the SFTP site and added the -b option pointing towards "get.bat". In this scenario, PuTTY will open the site, log on, and execute get.bat on the SFTP file system.
In my scenario, get.bat is the following
get.bat tells PuTTY to
1. 'cd /Properties5085' (change directory to Properties5085)
2. 'get 20120410 - 20120411.zip' (download the file)
3. 'C:\Share\Properties\20120410 - 20120411.zip' (put the file in C:\Share\Properties and name it 20120410 - 20120411.zip)
Now that we know how to execute PuTTY and how to point it to a .bat file that will make it do our bidding, we need to automate the initial PuTTY command by taking this:
and pasting it in to Notepad. Save the file as PuTTY.bat.
This leaves you with two .bat files. The first (PuTTY.bat) will tell PuTTY to log in and pass things of to the second .bat file. The second .bat file (get.bat) will download the specified file.
There are many creative ways to launch this process but the best is to simply launch PuTTY.bat from windows scheduler. Many of you are thinking, 'I can just launch this from an EXECUTE command from a QlikView script'. That is true but doesn't work so well in a Server/Publisher environment.
Server/Publisher doesn't know when a .bat file is done running so it will just hang until it's timed out. There are ways around this but are outside of the scope of this talk.
In closing, many of you will have noticed that the file I've downloaded in this example is a .zip file that can't be read directly in to QlikView. I'll be writing up an explanation on how that was handled as well. Until then...
