Connecting QlikView to the Zeit Online API Using QVSource

Zeit Online, the online version of one of Germany's leading newspapers, has released a beta version of an API to their content and I couldn't resist a quick play and blog post showing how easy it is to connect QlikView to the API using QVSource.

From their own developer website:

Via the API you can access articles and corresponding metadata from the ZEIT archive dating back to 1946 and from ZEIT ONLINE. We invite everybody interested in programming, data analysis and visualization to explore the index of hundreds of thousands articles. The API can be used for free for non-commercial use cases. Please refer to the terms of use (German only at the moment).

An API key can be generated in seconds - they simply ask for your name and email address and once generated their online API explorer makes it quick and easy to build and test queries.

I started with a simple query by selecting content from the drop down of options in the API explorer (click on the word after GET/ to see the drop down) and entering merkel for the search query as highlighted below. Clicking the Send request button then quickly returned back some results in json.

A little bit of research shows that only the parameters I have filled out get passed to the API - so the actual request is:
http://api.zeit.de/content?q=merkel

And scrolling to the bottom of the results reveal there are actually 5376 results in total but only 10 returned:

To get all the results, we simply update the limit parameter to something which will capture everything:

 

The request to the API has now become:
http://api.zeit.de/content?q=merkel&limit=6000

We can now use the QVSource Web Connector to connect QlikView to this API. There is just one more thing we should be aware of which is that the API requires that the API key is passed either as an X-Authorization header or via a api_key parameter. The former is preferred by the API but the second is supported and makes our job even easier - so our API request now becomes:

http://api.zeit.de/content?q=merkel&limit=6000&api_key=[INSERT YOUR API KEY HERE]

And you can even test this in a browser.

We now open up the Web Connector in QVSource and enter this url as a GET request:

And on the Data tab we can now check the RawResponse table to check the data is coming back as expected.

There are two ways we can get this data into QlikView now. The first is to use the JsonToXmlRaw table - this uses QVSource to convert the JSON to XML and then provides a new URL for the request which can be used in QlikView as a Web File data source. This is shown below:

We can simply copy the URL highlighted above and use it in the QlikView load script Web File dialog as shown below:

A more elegant solution is to use the QVSource JsonToTable table. To use this we must first do a quick simple analysis of the JsonToXmlRaw request, to do this we can take the URL generated above and browse to it in Internet Explorer where we get a nicely formatted XML response:

From this is should be quite clear that each row in the table we want is an instance of the matches element, and the XPATH to select these elements is then DATA/matches. To use this we need to enter it into the config screen of the connector as shown:

Now we have done this we can use the JsonToTable table in QVSource and get a nicely formatted table:

And the auto generated script to load this into QlikView as a QVX file:

We can now easily load this into QlikView and start analysing:

(Note that I actually changed the search term to 'angela merkel' for this final chart).

This is really just the start of what could be done with QlikView + QVSource. For example, QVSource also has a powerful Text Analytics and Sentiment Analysis Connector which could be used to score the article snippets and headlines for sentiment, or to extract entities such as categories and concepts.

And of course QVSource has connectors to Facebook, Twitter, YouTube and a ton of other sources which would allow you to bring in and mash up data from all sorts from other places.

Zeit are inviting users to show what can be done with their API - the their words:

We invite you to share your apps, experiments and experiences with the API. We will highlight interesting projects in the App Gallery section. Wir laden Sie ein, uns Ihre Umsetzungen, Experimente und Erfahrungen mit der API mitzuteilen.

So it would be great to see what the QlikView community could do with the API. Don't forget you can register for a free fully functional evaluation edition of QVSource here.

Page List