LivingQlik Roots: Qlik Sense – Loading Multiple Data Sources

Featured Image - Qlik Sense - Loading from Multiple Data Sources

Qlik Sense: Loading Multiple Data Sources into a single application

One of the key benefits of the Qlik platform is the ability to load and associate data from a variety of data sources. In fact, this is a feature that truly differentiates Qlik from most other BI platforms. In this post I will load data into Qlik Sense from several different sources and then associate the relevant data to complete a usable data model. I will use some “non-traditional” data sources to keep it interesting. In addition, I will utilize the Data Manager to load in most of the data.

Do you want to see this done in real time? Register for our live webinar below.

Malaria – A Killer Worthy of Our Analysis

End Malaria ImageOur data will focus on the current state of malaria cases and deaths by country. Malaria is a disease passed to people by mosquitos. In most developed countries malaria has been completely eradicated, but in primarily African countries, malaria infects 200 million people and kills about 400,000 each year, mostly children. Malaria is completely treatable, but the areas where it spreads easily, lack both the funds and proximity to proper medical care. It is a topic worthy of analysis. CDC

Data Source Variety is the Spice of Life

I compiled some data from several mediums to create our analysis story.

  • Qlik Data Market: Economy
  • Web File (HTML)
  • Microsoft Excel
  • Qlik Web Connector: Twitter Connector

Data Source 1: Country GDP and Population – Qlik Sense Data Market

I wanted to start with some baseline specs for GDP and Population along with the list of countries and their shape information (so we can create maps). You can easily get this data by tapping into the Qlik Data Market. In addition to the analytics tools we know and love, Qlik also is in the business of curating and selling data to utilize within your apps. There are several price levels that are primarily determined by the level of detail that you require. I am getting basic data by country so, I will be using my favorite level…free. There are several categories of data from currency exchange rates to weather. Today, we are using data from the Economy category. There are 2 ways to load data into Qlik Sense. Using “Add data” will take you to a simple visual tool called the Data Manager. The “Data load editor” will take you to the text-based script editor. I was “raised” on the normal script editor, but just for fun, let’s try the Qlik Data Manager. Qlik Sense - Data Loader Options Selecting Qlik Data Market will give us our package options. I am opting for the free version in this case.

Note: If you are on a server, the Data Market must be “turned on” in the QMC by accepting the license agreement and/or licensing the package you have purchased (if not using the free version). In the QMC go to License and Tokens > Qlik DataMarket.

I will start with my country list. Select Economy > Selected development indicators. In the “Select data to load” screen, I checked the Country box, and then went down to year and selected Most recent. Then select Indicator. I chose to look at total GDP, GDP per capita and total population. Now select Prepare data on the bottom of the screen. Qlik Sense - Data Market Selections - Country Qlik Sense - Data Market Selections - Year Qlik Sense - Data Market Selections - Metrics Now you will be presented with a visual preview of the data that will be loaded into Qlik Sense. Selecting one of the circles will display a preview of the actual data. Qlik Sense - visual Data Manager View Qlik Sense has decided that the data will be broken into two tables. Clicking on the “connecting rod” will show you how the two tables will be related. The data contains an alphanumeric ID field that joins the metric data to the country names and area. Press load data in the upper-right to populate the data model. Qlik Sense - Data Manager Connecting Field Go to the data model viewer to see the tables and preview the data. It looks like we have 214 countries, their areas, GDP and population…A great start. Qlik Sense - Data Model View

Data Source 2: Malaria Cases and Deaths by Country – Web Page Table

Our second data source will be a web page that has some tables in it. I found a site that contains current malaria statistics by country. We will use these tables in our Qlik Sense data model.

https://www.dred.com/uk/malaria-map.html

There is some added complication here because it appears the data is divided into a table for each continent. Altogether, there are 5 tables for us to suck in to Qlik Sense. Dr. Ed Malaria Map So let’s go back to our Data manager. In the right side, selecting the plus will allow you to bring in a new source. This time, I will click on Connect my data and scroll down to Web file. Enter the URL from above and name the source and we can continue. Qlik Sense - Web File Connector The preview will select the first table it finds on the page. This data looks good. Because we need the rest of the continents I will also select the other tables up to number 5. If you select the 6th table, you can tell from the preview pane that it does not contain any data. Select Prepare data. Qlik Sense - Selecting Web Tables Once you are in the visual preview, you might notice there is no connection between the new table and our existing Geographic information. You can highlight each table and figure out how they should be connected but it is probably easier to let Qlik Sense do it for you. Select the magician’s wand on the right side and “watch the magic happen”. Selecting the connecting rod between the two tables will reveal that Qlik Sense connected them on the Country field, which sounds fine to me. Select Load data to run the generated script. Qlik Sense - Web File Connecting Field

Validate your data: Let’s create some objects

Generally, after I have loaded a few tables, it is time to do a little “sanity check”. Putting a few tables on the page will tell us if the data is associated correctly and any issues will become obvious. I created some master dimensions to do some basic math and then created a table to look how my countries are lining up. It became apparent that there was a minor issue. Most of the countries are fine but there are a few that seem to be repeated and don’t line up when looking at population that came from the Qlik Data Market and the malaria data that came from the web page. It looks like a few of the country names are not matching. And because this is the field that associate the tables together, we are seeing some inconsistency. For example, Congo and Democratic Republic of Congo (2 distinct nations) each have 2 rows in this table. We will need to solve this problem. Qlik Sense - Congo Repeats

Data Source 3: Country Mapping Table – MS Excel

To solve this problem, I will just “rename” the countries that exhibit this problem. So I manually went through the list and created a 2-column table in Microsoft Excel. Excel - Country Mapping

We will use a concept called “mapping load” to get this into Qlik Sense. We will take the Malaria Data we loaded from the web page and if we find a match for the Country field in the data with the first column in the spreadsheet, we will replace that country name with what is in column two. To learn more about mapping load read this post: LivingQlik Roots: The Complete Guide to QlikView Mapping Load Now when it comes to some of the more complex scripting operations, the visual Data Manager simply won’t cut it. We will have to go to the Data load editor. And where should we write this script? You could add this script to the existing Auto-generated section, but I would suggest we do this in a completely separate section of the script. This is because if you choose to edit any script that was composed using the visual Data Manager, you will break the ability to go back to that method and make changes.

Note: Editing the Auto-generated section of the script will break the ability to go back to the visual Data Manager to adjust that data. For this reason alone, I generally choose to bypass the Data Manager all together, performing all my data modelling in the Data load editor.

In the Data connections section on the right, click Create new connection, and then select Folder. If this option is not available on your server, you will need to ask an admin to give you this ability in the QMC. Navigate to the correct folder, name it and you are good. disparate-data-ss16 I will use the select icon on the new connection and then choose the Country Mapping spreadsheet. There is nothing I need to do in the preview except Insert script. I can see Qlik Sense inserted the script for me. I will now adjust to map this spreadsheet to my Malaria data. I first use the mapping keyword in the inserted load statement. Then I will load the Malaria data from the auto-generated section over again, this time changing the Country field based on the map. Qlik Sense - Excel Mapping Load Load the data again and then look at the table in our sheet. Did it fix the issue? You might have to change the dimension since we changed it from the key name to “Country”. Now I only have 2 entries for the countries with “Congo” in the name. Perfect. Qlik Sense - Congo down to 2 entries

Data Source 4: #Malaria on Twitter – Qlik Web Connectors

I wanted to add a little more context to this dashboard. It might be good to see what people are saying about malaria on Twitter. This will add context and depth to our data. The Qlik Web Connectors are PREMIUM connectors. They are sold through a partner or directly from Qlik on a subscription basis. Because I work with a partner, I have access to this connector, but you might not be able to replicate this. So again, I can navigate to the Data load editor and create a new section of my script for this data source. The Qlik Web Connectors operate a little differently. There are no wizards in the Qlik Sense app to assist. Instead we will use a web page that comes with the Qlik Web Connector package that will help us authenticate with the data provider and create script which we can then copy and paste into our Qlik Sense application. Qlik Sense - Twitter Script Here is the completed data model. We will leave the Twitter data unassociated since it is related to the rest of the data only by virtue of being related to the word “Malaria”. Qlik Sense Final Data Model

Final Thoughts: Analyzing Malaria – The Dashboard

We can now build out any dashboard objects we want. I built out a few pages just to prove the validity of the data model. You can get the entire qvf file here: Malaria-Analysis.qvf Qlik Sense - Malaria Dashboard 1 Qlik Sense - Malaria Dashboard 2 Qlik Sense - Malaria Dashboard 3 So the ability to bring several data sources into a single Qlik Sense application is one of the many reasons why I love the Qlik platform. I know of no other Business Intelligence tool that will allow you to bring in data from so many varied sources and combine them so easily to create a real analytical application. There is no need for me to load all the data into SQL and perform dramatic, time-consuming ETL. All the techniques are “in the box”. Very cool stuff.

Do you want to see this done in real time?

Visual Data Group will be hosting a webinar to go over this process live in real time for those that are interested in exploring this further. I will follow the spirit of this post, building this data model live, and then (not withstanding demo demons) do some Q&A. You can register for this event here:

Click here to register for this event

Help LivingQlik Fight Malaria

Normally I ask you to leave a comment or something very trivial. This time I have a much more important ask. Please help me support a really awesome charity: Against Malaria Logo

Against Malaria Foundation

There is strong evidence that distributing mosquito nets reduces child mortality and overall malaria cases in the hardest hit regions. Against Malaria Foundation invests all your donation into the distribution of long-lasting insecticide-treated nets to the most heavily affected areas. GiveWell features AMF as one of their Top Rated Charities so you KNOW your donation has the greatest possible effect.

Please join LivingQlik and save some lives:

Against Malaria Foundation

Happy Qliking!

2 comments… add one
  • Reply Sue Penick January 9, 2017, 2:16 pm

    Does Qlik Data Market work with QlikView? do you have instructions for connecting to free data for QlikView?

  • Reply Aaron Couron January 9, 2017, 5:13 pm

    Hi Sue,
    I haven’t personally tried to load data from the Qlik Data Market into QlikView, but I can see there is a connector you could download that will provide you the means to do this:
    http://market.qlik.com/qlik-datamarket-connector-for-qlikview.html
    Thanks for the comment,
    Aaron

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons