Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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!
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)
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!
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.
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
This is my data file. I do not see a array name here. Can you please help here?
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