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

[Frank_Hartmann]

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

[#3]

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

[parpatra]

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!

[parpatra]

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!

[petter]

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.

[Frank_Hartmann]

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

[parpatra]

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

[Frank_Hartmann]

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