Qlikview and Json api

[kris_vliegen]

Hi all,

I have a reload script to add googlemaps data out of an api in a QVD.

This is the code

LOAD

  status,

  '$(k)' as %Customer,

    [result/geometry/location/lat] as latitude,

    [result/geometry/location/lng] as longitude

  FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

But now I would like to get some information from an other side but the data I get back is in JSon.

How can I translate this in the script?

Regards,

Kris

[Gysbert_Wassenaar]

Perhaps using QVSource is preferable to reinventing the wheel: General JSON/XML/SOAP Web API Connector

[Alexander_Thor]

QlikView can't parse JSON natively as it's by design not a flat data structure. We do have a XML connector/parser however so you never know for the future...

However as Gysbert pointed out there are several partner offerings out there which can act as a middle layer for your JSON data, QV Source being one of them that I have used a lot.

[Frank_Hartmann · Accepted solution]

paste the following code to your module and activate jscript on the left of module window:

function getRow(obj) {

    var str = '';

    for (var p in obj) {

        if (obj.hasOwnProperty(p)) {

            str += '"' + obj

+ '",';

        }

    }

    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) + '\n';

}

return csv.slice(0, -1);

}

after that you can load the json data in the script by function json2csv:

    Tmp:

Load

     json2csv(@1, '*******') as XXXX_csv

From [XXXXXXXXXXX./usecases/$(iUseCase)/$(sDate)/report.json] (txt, utf8, no labels, delimiter is '');

[kris_vliegen]

Thanks Frank.

I'm trying to use your solution.

I only need the url from the image. (after image_url)

How do I do this in the code?

Regards,

Kris

[Gysbert_Wassenaar]

Try using the textbetween function:

LOAD TextBetween( XXXX_csv , 'image_url":"','","') as image_url;

LOAD json2csv(@1, 'image_url') as XXXX_csv

From .....

All the quotes make it hard to see but the three parameters for the textbetween function are:

1. XXXX_csv
2. image_url":"
3. ","

The second and third parameter are text values are therefore enclosed in single quotes in the textbetween function.

[kris_vliegen]

Thanks Gysebert.

This is working great!

[Gysbert_Wassenaar]

Actually, if your source json file contains only one image_url value you could try loading it as a text file using a WHERE clause:

LOAD TextBetween(@1, 'image_url":"','","') as image_url

FROM [http://...etc] (txt, utf8, ...)

WHERE substringcount(@1,'image_url":"');

[Alexander_Thor]

If you do decide to parse the text straight up I would recommend setting the ErrorMode and a safety check on ScriptError so your entire reload won't halt on parse errors but do halt on major errors, such as the endpoint being down or moved.

[#10]

Hi all,

I just tried to use the above to read data from a different API, but there's this (by now very annoying) message saying:

Cannot open file

Wheather:

LOAD * FROM_FIELD (Tmp, CSV)

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

Here's what I did:

• Copy-past the jscript in the Editor
• Activate jscript on the left
• Created the script:

          Tmp:

          Load

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

          From [http://api.openweathermap.org/data/2.5/weather?q=London,uk] (txt, utf8, no labels, delimiter is '');

          Weather:

          LOAD * FROM_FIELD (Tmp, CSV)

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

          Drop Table Tmp;

It seems as if no data is obtained from the API, while pasting this API in the browser does show data in JSON.

Any help is very much appreciated!

Jasper