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
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 '');
Perhaps using QVSource is preferable to reinventing the wheel: General JSON/XML/SOAP Web API Connector
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.
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 '');
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
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:
The second and third parameter are text values are therefore enclosed in single quotes in the textbetween function.
Thanks Gysebert.
This is working great!
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":"');
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.
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:
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