topic Re: Qlikview and Json api in QlikView

Qlikview and Json api

kris_vliegen — Fri, 30 Jan 2015 15:48:53 GMT

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

Re: Qlikview and Json api

Gysbert_Wassenaar — Fri, 30 Jan 2015 18:31:48 GMT

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

Re: Qlikview and Json api

Alexander_Thor — Fri, 30 Jan 2015 18:50:04 GMT

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.

Re: Qlikview and Json api

Frank_Hartmann — Fri, 30 Jan 2015 19:05:23 GMT

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 '');

Re: Qlikview and Json api

kris_vliegen — Mon, 02 Feb 2015 09:17:48 GMT

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

Re: Qlikview and Json api

Gysbert_Wassenaar — Mon, 02 Feb 2015 10:15:25 GMT

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:

XXXX_csv
image_url":"
","

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

Re: Qlikview and Json api

kris_vliegen — Mon, 02 Feb 2015 10:40:50 GMT

Thanks Gysebert.

This is working great!

Re: Qlikview and Json api

Gysbert_Wassenaar — Tue, 03 Feb 2015 09:13:30 GMT

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":"');

Re: Qlikview and Json api

Alexander_Thor — Tue, 10 Feb 2015 20:54:09 GMT

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.

Re: Qlikview and Json api

Wed, 15 Apr 2015 12:03:01 GMT

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

Re: Qlikview and Json api

Frank_Hartmann — Thu, 16 Apr 2015 13:37:57 GMT

Hi Jasper,

try like this:

Tmp:

Load

json2csv(@1, 'weather') as CSV_

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

Quotes:

LOAD * FROM_FIELD (Tmp, CSV_)

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

Drop Table Tmp;

hope it helps

Re: Qlikview and Json api

Fri, 24 Apr 2015 10:03:38 GMT

Hi Frank,

I was able to get some data, but not able to transform it to csv. So I decided to do the interpretation of the JSON in QV script.

Thanks.

Jasper

Re: Qlikview and Json api

parpatra — Wed, 10 Jun 2015 03:44:56 GMT

I am trying to load a JSON file using the steps as mentioned by Fank. But I am not able to load that in qlikview. It reads the data in first step, but errors out when loading the second step as 'cannot read tmp'.

file source Index of /sample/ (city data)

script used :

Tmp:

Load

json2csv(@1, 'weather') as CSV_

From [xxx\city\city.list.json] (txt, utf8, no labels, delimiter is '');

Quotes:

LOAD * FROM_FIELD (Tmp, CSV_)

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

Drop Table Tmp;

Thanks in advance!

Re: Qlikview and Json api

parpatra — Wed, 10 Jun 2015 14:50:36 GMT

am trying to load a JSON file using the steps as mentioned by Fank. But I am not able to load that in qlikview. It reads the data in first step, but errors out when loading the second step as 'cannot read tmp'.

file source (see attached city data)

http://78.46.48.103/sample/

script used :

Tmp:

Load

json2csv(@1, 'weather') as CSV_

From [xxx\city\city.list.json] (txt, utf8, no labels, delimiter is '');

Quotes:

LOAD * FROM_FIELD (Tmp, CSV_)

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

Drop Table Tmp;

Thanks in advance!

Re: Qlikview and Json api

petter — Wed, 10 Jun 2015 16:23:19 GMT

You shouldn't post new questions on a thread that has been answered. Post your question as a new thread and referring to this thread instead with a link.

Re: Qlikview and Json api

Frank_Hartmann — Wed, 10 Jun 2015 19:50:47 GMT

Hi Partha

in the line:

-----> json2csv(@1, 'weather') as CSV_

you need to name the array in which the data you are looking for is placed.

In my example the array was named "weather". Just look for an array name in your json file

and QV should get some data from it

-----> json2csv(@1, 'arrayname') as CSV_

hope it helps

Re: Qlikview and Json api

parpatra — Wed, 10 Jun 2015 22:26:36 GMT

This is my data file. I do not see a array name here. Can you please help here?

Re: Qlikview and Json api

Frank_Hartmann — Thu, 11 Jun 2015 07:21:17 GMT

the structure of you json file does not fit. In your json file there are only curly brackets, but the data within the array should be surrounded by theses brackets [ ]

you can only use this macro if the json output has the correct format.

for example see the attached json file (there are two arrays: responseTime and Availibility)

hope this helps