Welcome Guest, you are in: Login

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

In this example we will use the QVSource Mashape Connector to access the rome2rio API on Mashape which will allow us to get routing information between two named places into our application.

This could be useful, for example, to calculate journey times from a central delivery depot to a number of satellite stores.

The first thing to do is notice from the documentation what the end point is and what the parameter names and required parameters are.

From the screen shot below:

Image

We can see that the only required parameters are dName and oName (for the origin and destiniation) and that the end point for this Search API call is:
https://rome2rio12.p.mashape.com/Search.

From this we can construct the following example URL for requesting routing information between Barcelona and Madrid:
https://rome2rio12.p.mashape.com/Search?dName=Madrid,Spain&oName=Barcelona,Spain

Of course, in a real QlikView application the origin and destination values would be variables which we extracted from one of the other tables in your QlikView application, e.g. something like:
https://rome2rio12.p.mashape.com/Search?dName=$(vDestination)&oName=$(vSource),Spain

We can now enter this information into the QVSource Mashape Connector:

Image

Note that we have also entered our Mashape API key and also set -1 for the caching parameter - in this example we are assuming that the information we get back from the API for this pair of locations will not change - e.g. the journey time for a given mode of transport.

We can now move over to the Data tab and check the response. As the rome2rio API returns JSON data we will use the JsonToXmlRaw table to map this to XML which we can then load natively into QlikView:

Image

NOTE: We could also use the JsonToTable table here, possibly specifying an XPATH expression on the connector config tab, to convert the XML directly into a table which QlikView can read, however in this case we will use QlikView's XML parsing functionality as shown below.

We now copy the URL for this request and use it as a Web File data source in QlikView:

Image

We can then tweak the script generated by QlikView to extract just the routes information (or whatever is needed), e.g.:


let vOrigin = 'Barcelona,Spain';
let vDest = 'Madrid,Spain';

routes:
LOAD name,
    distance,
    duration
FROM [http://localhost:5555/QVSource/MashapeConnector/?table=JsonToXmlRaw&appID=&cacheTimeInHours=-1&verb=get&url=https%3a%2f%2frome2rio12.p.mashape.com%2fSearch%3fdName%3d$(vDest)%26oName%3d$(vOrigin)]
(XmlSimple, Table is [DATA/routes]);


It is more likely that the locations will come from another table in your QlikView application - we simulate this here with an inline table:



Locations:
LOAD * INLINE [
oName, dName
"Barcelona,Spain", "Madrid,Spain"
"Barcelona,Spain", "Granada,Spain"
"Barcelona,Spain", "Seville,Spain"
];

LET noRows = NoOfRows('Locations');
    
for i=0 to $(noRows)-1
     
	let vOrigin = peek('oName', $(i), 'Locations');
	let vDest = peek('dName', $(i), 'Locations');

	trace 'Calculating $(vOrigin) -> $(vDest)';
	
	routes:
	LOAD
		'$(vOrigin) -> $(vDest)' as journey,
		name,
	    distance,
	    duration
	FROM [http://localhost:5555/QVSource/MashapeConnector/?table=JsonToXmlRaw&appID=&cacheTimeInHours=-1&verb=get&url=https%3a%2f%2frome2rio12.p.mashape.com%2fSearch%3fdName%3d$(vDest)%26oName%3d$(vOrigin)]
	(XmlSimple, Table is [DATA/routes]);

next


Running this results in the following which could be further reduced to only include, say driving times:

Image

The API provides a great deal more information than we are extracting from the above and also supports a richer set of input parameters than we use in this example. However hopefully the above information will get you started on getting routing information into your QlikView app.


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