Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

Please note - There is a beta feature and we are still investigating how to better deal with these sorts of Twitter queries from QlikView.

You will need QVSource 1.5.0.0 or later to use this feature. A more complete example can now be found over on GitHub.

This page describes a new feature we are working on to overcome an challenge which sometimes arises in the QVSource + QlikView environment.


The Challenge

The issue that this new feature aims to address is primarily a result of the fact that QlikView is designed to load one single 'flat' table at a time whereas APIs often either return more hierarchical unstructured data and/or return other important meta information related to the request or data which comes back.

Some more concrete examples might include:

  • Information on the rate limiting which might have occurred whilst the data was being retrieved from the API (this might mean that the table does not 'yet' contain all the data expected due to API quota limitations.
  • Errors which might have been returned from the API during the request affecting some or all of the results.
  • A single API returning a hierarchy of data. For example the Facebook API Feed might return a list of posts but also for each post it might also return the first X comments and the first X likes, meaning we have three tables not just one.

It would be useful in QVSource to have a more controlled and structured way of dealing with the above scenarios which can be managed from the QlikView load script.

It is worth noting however that the issues noted above only occur for a small number of tables.

An Example

Note that this example below uses QVSource version 1.5.0.0. We have also noticed that this table is currently not working correctly and at present should be considered a beta preview feature.

A concrete example is the FollowerIds table in the Twitter Connector for QlikView. The information note for this table states: Returns a collection of user IDs for every user following the specified user. 15 APIs calls can be made for this resource per 15 minute window and each response can contain up to 5000 IDs.

So in the background QVSource will attempt to make up to 15 API calls to Twitter for a given user returning up to a maximum of 15 x 5000 = 75,000 ids. If there are more followers than this however QVSource will receive a 'quota limit exceeded' error. It will still return all the data it has managed to retrieve, and if the user is running the table in QVSource itself they will be shown a message explaining what has happened.

So we have the problem that:
  • We would like a way to know that this has happened in our QlikView load script (i.e. a quota limit has been hit).
  • We need to be able to pick up where we left off when the quota limit is reset (every 15 minutes in Twitter) when the next QlikView reload takes place.

To tackle this we are working on a concept of being able to call 'secondary' or follow up tables after the initial 'main' table has run to get at the information we need. The way we tie these tables together is via a new 'requestStamp' parameter.

So in this case, let's imagine we want to retrieve all the follower ids for a Twitter account with a very large number of followers. The screen shot below highlights two main things:

  • A new Request Stamp parameter.
  • A new note for the table explaining that there is a related table which can be used in conjunction with this.

Image

Note that the request stamp can be anything we like. The only real requirement is that there are no other qlikview load processes running at the same time using the same combination of connector, table name and stamp which for the vast majority of users is very unlikely.

Now when we run this table, it will download 75,000 followers at the end of which the user will receive the following message:

Image

And it can be seen that we have our first 75,000 rows.

Image


TwitterConnectorV2_FollowerIds:
LOAD
	id as FollowerIds_id
FROM
[http://localhost:5555/QVSource/TwitterConnectorV2/?table=FollowerIds&appID=&userName=cocacola&requestStamp=myAppsFollowerLookup]
(qvx);

And that the new requestStamp is part of the load script. We can now use this requestStamp in the related Post_FollowerIds_Info table:

Image

Where we can see that indeed the results in this previous table were rate limited, what cursor value to use when we next use the FollowerIds table and when we can next use this table.

Note also that for this particular table the seconds_to_reset column is 'live' meaning each time we request it the value is updated. It should be possible then in theory to have a loop in our QlikView load script which checks this periodically until it is less than zero and then we can once again request data from the FollowerIds table.

Another strategy would be to store the next_cursor value in a QVD file and arrange for the application to be reloaded say, every 20 minutes, each time using the next_cursor value from the previous load.

Below is one example of how a more complete script could be developed. You can download QlikView application containing this demo here.

(Note, there should be an embedded GitHub 'Gist' below - if it does not show please try refreshing your browser.)

Next Steps

At present this is a working concept and has only been implemented for this table. Once we receive feedback on the idea we will see how it can be improved and then consider rolling it out to a small number of other specific tables and connectors where it might make sense.
  Name Size
- 75000 rows.png 66.43 KB
- follower lookup.png 54.07 KB
- post info table.png 39.48 KB
- quota limit error.png 8.75 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd