Consuming Web Services that Return JSON

There are hundreds of web services that return data in either XML or JSON formats. XML is understood by QlikView and is straightforward to load. However QlikView cannot directly parse JSON formatted data which is becoming more and more popular due its simplicity and ease of consumption by browser apps written in Javascript.

There are many different ways to connect a new data source, such as a web service that returns JSON, to QlikView. You can write an external program in C#, PowerShell, Python etc. to talk to the web service and dump the data into a format such as CSV or XML that QlikView can read. You can use the Connector API to create a connector. You can also use an external program to use the Automation API to pump the data in via DynamicUpdate. Here is a fourth approach that is simple, requires no external components and does not create any files. It also highlights the usefulness of loading from a Field.

Here is the Javascript code to parse JSON. Add it to the Module in your QVW document.

function getRow(obj) {

    var str = '';

    for (var p in obj) {

        if (obj.hasOwnProperty(p)) {

            str += '"' + obj[p] + '",';

        }

    }

    return str.slice(0, -1);

}

function getHeading(obj) {

    var str = '';

    for (var p in obj) {

        if (obj.hasOwnProperty(p)) {

            str += '"' + p + '",';

        }

    }

    return str.slice(0, -1);

}

function json2csv(jsonText, arrayName) {

var object = eval('(' + jsonText + ')');

eval( "var array = object." + arrayName );

var csv = '';

csv += getHeading(array[0]) + '\n';

for (var i=0; i<array.length; i++) {

csv += getRow(array[i]) + '\n';

}

return csv.slice(0, -1);

}

Here is an example that loads stock quotes for your favorite companies from the Yahoo Query Language (YQL) API. YQL can be told to return in XML as well, but I am asking it to return JSON here. Note that the entire From for Tmp table load is one long line!

Tmp:

Load

json2csv(@1, 'query.results.quote') as CSV

From [http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22YHOO%22%2C%22AAPL%22%2C%22GOOG%22%2C%22MSFT%22)%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=json] (txt, utf8, no labels, delimiter is '');

Quotes:

LOAD * FROM_FIELD (Tmp, CSV)

(txt, utf8, embedded labels, delimiter is ',', msq);

Drop Table Tmp;