Connecting to web services with QlikView

iStock-800364688.jpg

Over the past while I have been on a bit of a Web Services kick with Qlikview. Unfortunately for me the software’s standard GET connection didn’t really allow me to return much data directly into Qlikview.   I was running across a couple of different issues…either the Web Service returned JSON or it required a POST command.   As Qlikview does not support these types of services, I had to do some home brewing to get them to work in Qlikview. After some thought and some time,  I have created a Java framework that can be customized by anyone who needs it.  Hopefully I’ve made your life easier if you ever come across this scenario!  All you’ll need is a little Java know how and access to Google.

The code base that was created has been uploaded to GitHub (see link below) and has been listed on http://branch.qlik.com.  While it is meant to get someone up and running fast, I wouldn’t consider it production material(use at your own risk), so please see fit to modify as needed!

The Java code is based on the testing framework provided as part of Jboss’s RESTEasy platform.  The webserver accepts http GET commands, and then does a translation to the appropriate http command, such as POST.  The data stream is fetched via Java and then returned to Qlikview in a friendly format (XML).

There are some sample proxies included.  The first is the /general/Get2Get_JSON2XML, where the code does a GET to GET proxy, but converts the JSON returned from the web service to XML so that Qlikview can understand. 

There are two proxies included for connecting to Infusionsoft.  These proxies can be accessed at ‘/Infusionsoft/DataQuery’ and ‘/Infusionsoft/DataCount/DataCount’.  Stayed tuned to future blog posts for further instructions on how to use these.

If you are trying to run the program for the first time follow these instructions.

  1. Download(or clone) the maven project from GitHub (see link below)
  2. Import the extracted project into Eclipse (or other IDE)
  3. Using the m2e plugin Maven Build – ‘clean compile package’ the imported project
  4. Once the project has built, you can used the Start.java test case to run the service.
  5. Once the service has started, you can use QlikView to connect to various web services.

 

To test the installation, we can try accessing geo information.  Using the /general/Get2Get_JSON2XML proxy, we can connect to the Data Scientist Tool Kit and get geo information based on a latitude and longitude.  This a pseudo reverse geocoding script for when you have the latitude and longitude but no city information.  

 

// Start of [37.769456,-122.429128] LOAD statements
politics:
LOAD name,
    friendly_type,
    code,
    type
FROM [http://localhost:8080/general/Get2Get_JSON2XML?url=http://www.datasciencetoolkit.org/coordinates2politics/37.769456,-122.429128] (XmlSimple, Table is [array/politics]);
 
location:
LOAD longitude,
    latitude,
    %Key_location_6B88F4B6C3E6C05D    // Key for this table: array/location
FROM [http://localhost:8080/general/Get2Get_JSON2XML?url=http://www.datasciencetoolkit.org/coordinates2politics/37.769456,-122.429128] (XmlSimple, Table is [array/location]);
// End of [37.769456,-122.429128] LOAD statements 

 

 

And there you have it! A quick example of how you can access non tradidtional web services with Qlikview., all you have to do is take it and run!  

Stay posted for blogs containing more innovative out of the box QlikView solutions!

 

Downloads:

Lucas

BizXcel Inc.