Loading Qonnections 2015 Instagram Posts Into Qlik Sense (Or QlikView)

In this post we will demonstrate using the new Web Edition of QVSource to load data from Instagram into Qlik Sense Desktop.

The QVSource Web Edition is currently in private beta but should be available for general (beta) download in June 2015. We are also currently hosting an invite only cloud hosted instance of QVSource Web Edition which a number of users are already testing out the new version with. If you would like to be considered for access to this please fill out this form.

NOTE: You can also try this out using the current QVSource Desktop Edition. Whichever version of QVSource you are using, when using Qlik Sense just remember that at present you will first need to make this minor config change in order to allow load requests from arbitrary URLs.

First of all we fire up QVSource and open the Instagram connector. We ensure we are authenticated with Instagram and then select the SearchForTags table and enter ‘qonnections’ in the Tag field. There are (at time of writing) 303 results for ‘qonnections’:

We can see that there are a few Instagram tags being used in relation to Qonnections, the most popular one being simply 'qonnections'. We can now go to the MediaByTag table and make sure that this tag name is entered as the tag to find Instagrams for and then run the table.

We then select the Qlik Sense tab and copy the script to the clipboard:

Now we fire up Qlik Sense Desktop, create a new app and open the data load editor:

We paste in the script, and click load data:

And run the reload:

Note that we do not necessarily get back as many results as suggested in the earlier table as the SearchForTags table above shows counts including images which are private or might have subsequently been deleted.

We then return to the app overview and create a new sheet:

We edit the sheet and drag a bar chart in:

Then add the dimension username:

...and a measure of count(id):

We then limit to the top 10:

...and complete the edit, giving us the top 10 posters:

We now create a new sheet and add a table with dimensions for user name and link; and a measure for sum(likes count):

We can then sort by likes (descending) to see the most liked posts and poster:

We click ‘done’ to view the results:

Now we create a new sheet and add a bar chart with create time (utc qlik date) as dimension and count(id) as measure. This can be sorted by the dimension (date) first and saved, showing the peaks in posting media around the annual Qonnections conference:

This demonstrates just how easy it is to pull data from instagram and quickly extract meaningful patterns and trends. This is just a simple example, in a real world example we might want to write QlikView/Qlik Sense load script to loop through a number of different tags which were used during the event. We could also extract a great deal more data such as comments and likes using the many other tables which the connector supports.

You will find a more complete example for QlikView over on our GitHub Page - note that although this is for QlikView the load script should also work fine in Qlik Sense giving you the same data model to build your user interface on top of.

You can request a fully functional free trial of QVSource from our and try out the 35+ Connectors it supports with QlikView or Qlik Sense.

Creating A QlikView Twitter Tracker Application In 30 Minutes

We just set up a Twitter tracker application (link at end) for the upcoming Qlik Qonnections Global Partner Summit in Orlando (which by the way we will be attending and sponsoring!) and I thought I would remind users how easy it is to do this using QlikView and QVSource.

Assuming you already are a QlikView user and have QlikView desktop up and running, the next thing of course is to make sure you also have QVSource. If not, you can grab a free fully functional trial here and find instructions on getting up and running here.

(Setting up QVSource is really easy - the application is delivered simply as a zipped folder, no installer, no components to register. Typically you will simply need to unzip the QVSource folder to somewhere on your local disk and double click the QVSource.exe then simply install your licence file.)

The next step is to make sure you are authenticated with the Twitter Connecotor - to do this first locate and double click the Twitter Connector in the list of Connectors:

Then on the Connector's config screen - click on the 'Authenticate' button:

You will then be redirected to Twitter's authentication screen where you can enter your account name and password. After completing this you should find a token has appeared next to the 'Authenticate' button.

If you like you can do a quick test here - enter a search term and click the 'Run Table / Generate Script' button and you should see matching Tweets returned:

This is all we need to do in QVSource now - we just need to make sure that it is left running in the background:

Wherever the QlikView reload is taking place.

Next we go to our GitHub site, specifically the QVSource-Twitter-Starter-App-For-QlikView and click the 'Download ZIP' button:

Unzip this file somewhere on your local machine, you should see something like this: 

For this post, we are only interested in config.txt and Twitter-Starter-App-Load-Script-And-Data-Model.qvw. Below are some notes on these and some of the other files and folders:

  • config.txt is simply a QlikView load script fragment referenced by the main QVW file. Having your config data here (such as search terms, timezone, sentiment API configuration etc.) means that you can often simply overwrite the QVW file with updated versions and continue your reloads without interruption (as long as the data model has not been updated between versions).
  • Twitter-Starter-App-Load-Script-And-Data-Model.qvw reads the contents of config.txt (which tells it, amongst other things, which Twitter search terms to monitor) and creates a set of QVD files containing all the data for the application. It is designed to be reloaded regularly (e.g. every 5 minutes) and only query Twitter for the newest results since the last reload.
  • Twitter-Starter-App-Load-Script-And-Data-Model-Alternative-UI.qvw is an alternative front end to the same set of QVDs. Once you have run Twitter-Starter-App-Load-Script-And-Data-Model.qvw.qvw once and generated the first set of QVDs you should be able to simply hit reload on this application to see a different view of the data.
  • The two sub folders contain the files which make up the two QVW applications and represent one way QlikView can be used with a source control system. If you were to open the QVW files and save them to an alternative location then these files would all be combined into the newly saved QVW application.

Open config.txt in your favourite text editor. We will now discuss the few minor changes we will make to get the application ready to reload.

First, locate the following section:

let vLocalTimeZone = 'UTC+01:00';
let vLocalReloadTime = date(ConvertToLocalTime(gmt(), '$(vLocalTimeZone)'), 'DD/MM/YYYY hh:mm');

From this site we can see that since the 9th March the timezone in Orlando is UTC - 4 hours so let's update our config file accordingly: 

let vLocalTimeZone = 'UTC-04:00';
let vLocalReloadTime = date(ConvertToLocalTime(gmt(), '$(vLocalTimeZone)'), 'DD/MM/YYYY hh:mm');

Next, find this section:

// A folder which QlikView has write access to and QVSource has read access 
// to. See http://www.qvsource.com/wiki/Synchronous-Asynchronous-And-Batch-Requests-Explained.ashx 
// for more info.
let vWorkingFolder = 'c:\QVSource\Temp\';

For certain operations, QVSource needs a folder where it can read and write temporary files. We generally recommend running QVSource from the folder c:\QVSource and having the above Temp subdirectory. However you are set up, you should make sure that the above variable points to a folder location which exists and which the QVSource.exe has read/write access to.

Now locate the TwitterSearchTerms inline table. We will edit this to be:

Grouping, SearchTerm
Qonnections, Qonnections,
Qlik, from:QlikView,
Qlik, QlikView,
Qlik, Qlik,
QVSource, qvsource
QVSource, from:QVSource ];

The application allows you to configure an arbitrary 'Grouping' field as well as a number of Twitter search terms for each.

As a minimum we want to track the official #Qonnections tag. As Qonnections is such a unique word we have excluded the hashtag so that we pick up any Tweets where the user has simply referred to it without the # too.

We are also interested in picking up some related Tweets so have included a number of other search terms. QlikView, Qlik and QVSource should be self explanatory.

The from:QlikView and from:QVSource search terms are added additionally to pick up any Tweets sent from these two accounts - this is because they might not actually contain any of the other search terms but might still be interesting to our analysis.

You should make sure that the following setting is 0. By default random Klout scores are generated as this greatly speeds up the load time during development and testing of the application.

let vUseRandomKloutScore = 0;

Finally, note the following section:

// Configure this table with one or more of the following sentiment APIs:
// Repustate
// Sentiment140
// AlchemyAPI
// Random
// NOTE:
// - You may need to ensure you have API keys configured in QVSource for the selections you make here with 
// enough quota to be able to score all the content.
// - The QVSource Sentiment Analysis & Text Analytics Connector also connects to other APIs which you can 
// of course also use however you would need to modify the load script accordingly.

You can see that the application is, by default, set to use the Sentiment140 API in the QVSource Sentiment Analysis & Text Analytics Connector which supports English and Spanish. You will see from the notes above this section of load script that you may also easily select some of the other APIs the connector supports.

That should be all we need to do - save config.txt and then open the Twitter-Starter-App-Load-Script-And-Data-Model.qvw and hit reload. When we first did this we ended up with the following:

This application has quite a rich data model, not only does each Tweet captured have a great deal of information but we also now have data on the Klout score for each user and a sentiment score for each Tweet. Additionally the application creates fields which indicate whether the Tweet is an original Tweet, a Retweet of a Tweet or a reply to a Tweet as well as extracting entities such as hashtags, links, pictures and stock ticker sumbols into separate tables.

The screen shot below (click to enlarge) shows the most important parts of the data model for this QlikView application:

Looking back at the application, if we select just the 'Qonnections' search term we can see that in fact the application only has 47 Tweets.

(Click to Enlarge)

But you can already see who the top Tweeters are, what the top hash tags are etc.

The reason there are only a small number of Tweets is that the Twitter Search REST API which QVSource uses only allows you to go back up to approx 1500 Tweets or 1 week for a given search term. Here we can see that we have hit the 1 week back limit.

However, we will now deploy this to our QlikView Server and set it up to reload regularly and over the coming weeks we should see all new Tweets accumulate into the QVD files and the application.

You can see this here!

And we look forward to meeting many of our partners in Orlando in a few weeks!

QVSource 1.5.2 Now Available

We have just released a new version of QVSource - The QlikView API Connector. This blog posts outlines the main changes

As always, we would also recommend you also read the notes on the "Change Log" tab of any connectors you are using for a comprehensive overview of changes.

New Connectors

  • SugarCRM: We have a brand new connector for SugarCRM, one of the most widely used CRM systems on the market. This connector allows QlikView users to load Accounts, Contacts, Leads, Opportunities, Forecasts, Meetings and Calls.

Updated Connectors

  • Facebook Insights V2: We have a completely revised V2 of our Facebook Insights Connector which uses the new UI style. This offers huge performance and usability improvements over the previous version (which is now deprecated and marked for removal) and we would recommend all users to upgrade. We have instructions here on upgrading from the previous version. Please note that the previous V1 of this Connector will be removed from QVSource in June 2014 so all users should upgrade no later than this.
  • MongoDB: We have a new version of our MongoDB Connector which uses the new UI style and the latest MongoDB Driver. The previous version of this Connector is now deprecated and although this Connector is still in beta would encourage all users to start using this new version and providing feedback or comments.
  • Google Calendars: We have a new version of our Google Calendar which uses the new UI style. This has a small but breaking change which is that the default data format is now QVX - we would recommend you regenerate your load scripts.
  • Google AdSense: We have a new version of our Google AdSense Connector which uses the new UI style and the latest API (v1.4) version. Please note that the previous V1 of this Connector will be removed from QVSource in June 2014 so all users should upgrade no later than this.

Removed/Deprecated Connectors

Of particular importance are the four removed Connectors listed below. If you are using these you must upgrade to start using the new versions.

  • The following Connectors have been removed as they have been replace/superseded with new improved versions:
  • Salesforce Connector: We have also deprecated our Salesforce Connector for QlikView. This is because Qlik have their own Connector to this already. We are not currently planning to remove the QVSource version from the product but are no longer actively maintaining or developing it. If you find the Qlik Salesforce Connector does not meet your requirements please get in touch.

Connector Updates

  • Google AdWords Connector: We have upgraded this by two AdWords API versions to the latest v201403. Please check the Report related updates here and here for changes and new features which you may be able to use in your reports.
  • Google Analytics Connector: We have fixed a minor bug where the WebProfiles table did not page correctly. This would only have affected users who had more than 1000 Web Profiles.
  • Google DFP Connector:
    • We have upgraded to the latest DFP API version v201403.
    • We have reduced memory utilisation and improved performance.
    • The Connector now makes more intelligent usage of the API quota limits.
    • The parameter input area should now scroll vertically.
  • Google Drive & Spreadsheets Connector: We have added the ability to specify a folder ID to move a newly created spreadsheet to.
  • Google Plus Connector:
    • The Person table has been renamed ObjectDetails (as it can return data for a Person or Page). For script generated using previous versions, the table People should be automatically mapped to this name. This table also has the following new fields: occupation, plusOneCount, circledByCount and primary_placesLived.
    • We have added a new ObjectDetailsAsXML table which returns all the details returned by the API as XML (ObjectDetails selects a subset of these to a structured table). This can then be natively parsed/loaded in QlikView.
  • Twitter Connector: We have added Post_UserLookup_Info and Post_UserLookupById_Info tables and an associated example application on GitHub here illustrating how they can be used. The purpose of these tables is to support the use case of loading information on a large number of followers or friends. Because the API only allows a limited number of calls per 15 minutes, if the account you are interested in has above a certain number of followers/friends then you need to accumulate this data over a number of reloads over a number of 15 minute windows.
  • Sentiment Analysis & Text Analytics Connector:  The ChatterBox option has been renamed to Sentimental. The Excitement table has been removed and an Anger table has been added for Chinese. These are changes based on the underlying API and not due to QVSource.
  • Facebook Fan Pages & Groups Connector:
    • The User and Person tables now always return a row, a new status column indicates any errors returned if there was an issue retrieving the data.
    • We have fixed a bug in the 'Don't return items before' feature which was sometimes not going back as far as it should.
    • We have fixed bug where the has_at_least_this_many_likes and has_at_least_this_many_comments fields always had a value one larger than the actual.
    • We have added comment_link and like_link columns to FeedItems and Posts tables. This contains a link which allows you to open the post in a browser from your QlikView application.
  • Blue Yonder Connector: We have upgraded this in line with the latest v1.14.0 and also changed the parameter input for the API version so the user can enter their own version number (rather than picking from a drop down).
  • General XML/JSON/SOAP Web Connector: We have added a new User Agent input field to allow this to be overridden.
  • You Tube Analytics Connector: This Connector has now been taken out of beta.
  • HEIDI Connector: The parameter input area should now scroll vertically.

QVSource On GitHub

You will now find most of our example and 'starter' applications over on our GitHub page here.

You don't need to know anything about GitHub to use them (just look for the 'Download ZIP' button for any applications you are interested in). However if you do have a free GitHub account you can use it to receive alerts for changes to the applications and also as a way of exploring the load scripts and their changes on the GitHub site itself (look for the LoadScript.txt file in the sub folder of the application you are interested in).

Download Latest

Where do I find this new release? If you are a QVSource customer or have requested a trial in the past you should see this new release at the personalised download link we should have sent you via email.

Before that - If you like what we are doing with QVSource and haven't already, please take a minute to rate us on QlikMarket or leave a recommendation on our LinkedIn product page.

Tracking The 2013 Argentine Legislative Election On Twitter With QlikView And QVSource

Just a quick heads up on a great application built by Ignacio Albano (@nachoalbano) and a QlikView partner in Argentina to track Twitter activity related to the upcoming Argentine legislative election.

The application is built using QlikView with QVSource providing data from Twitter. We have included a couple of screen shots below, but if you are quick (the elections fall at the end of October 2013), you can try the live application online here.

QVSource - The QlikView API Connector - contains connectors to a large number of web APIs, including Facebook, Twitter, YouTube, Google Analytics, Multiple Sentiment Analysis & Text Analytics APIs and many many more. You can request a free, fully functional trial, over on our website.

Page List