Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

JSON Loader

Luminary
Luminary

JSON Loader

Hey,

I hope I can solve a lot of peoples headache with a better way of loading JSON data.

This method works by loading the JSON data into an table with a single field, and then placing it into a variable and then converting the value into xml which then is loaded. Works very well but is still a bit for the technical people. I will not provide any support.

In order to make this work, we need to detect the XML definition after JSON to XML conversion. Open the JSON Loader.qvw Application and the JSON url.

Copy the JSON output into the vData variable, and click the JSON to XML button. Now take the output in the left side and copy that into a .xml file and load it from qlikview.

The output script should look similar to this:

TAG:

LOAD adminCode3,

    adminName2

FROM [Desktop\xml.xml] (XmlSimple, Table is [XML/POSTALCODES/TAG]);

From this we need to extract 2 things. The list of fields (place into vFields), and the XML tree to load (place into vXMLTree).

In the edit script set the variables based on the above:

Let vUrl = 'http://api.geonames.org/postalCodeLookupJSON?postalcode=6600&country=AT&username=demo';

Let vFields = 'adminCode3,adminName2';

Let vXMLTree = 'XML/POSTALCODES/TAG';

Cross fingers and reload

Do this for each JSON source.

Enjoy Torben Seebach 4C Management Consulting

Tags (2)
Labels (1)
Attachments
Comments
MVP
MVP

Hi Torben,

thanks for this solution which is very similar to my earlier posts, but a bit more complicated I think.

Anyways, we will come out the next days with a JSON proxy app which can stream any JSON data from REST endpoints or files directly into QlikView as XML stream.

With this JSON proxy web APIs like freebase.com or infochimps.com can be connected easily, also NoSQL DBs like MongoDB and CouchDB.

http://tiqview.tumblr.com/post/25374986317/this-is-a-first-impression-of-our-brand-new-json

The latest tests ar running now..

- Ralf

chrisbrain
Valued Contributor

We're also (still) looking at adding something to help with JSON into QVSource - something I said we would start investigating at the start of the year but got side tracked from with Connectors to specific services and APIs .

I think our solution will be most appropriate for users already using other QVSource Connectors.

chrisbrain
Valued Contributor

And here it is - first release of the QVSource solution for dealing with general JSON (and indeed XML) APIs: http://www.qvsource.com/wiki/General-Web-Connector-For-QlikView.ashx   It's available now in the latest nightly build (1.2.4.16) and should be pushed into the next 'official' release assuming it's working out for everyone.

MVP
MVP
saumyashah90
Valued Contributor

Any Ways we can load JSON data to qlikview without using QVSource?

Not applicable

Can you share your loading JSON data effort ?

Not applicable

I'm interested in something of this sort too (directly from QV). I'll be taken a look at the solution proposed here, but right of the bat I wasn't able to see it working yet.

Any other versions of this app (working) or other ideas out there?

chme02
New Contributor II

Hi,

Great script. Any thoughts on how it could be adapted to cope with JSON Array?

Example:

{"count": 39, "values": [{"kpi": "N00914", "municipality": "1283", "period": 2013, "values": [{"count": 1, "gender": "K", "status": "", "value": 70.006373}, {"count": 1, "gender": "M", "status": "", "value": 73.703903}, {"count": 1, "gender": "T", "status": "", "value": 71.850318}]}

Not applicable

Very Helpful...Thank you !!

swijnans
New Contributor III

I cant use the new connector because my JSON files are delivered to me in a zip file. I get the file from the web, unzip it, and have to load the JSON files from directory. In other words I have to revert back to the script. It works fine locally, but fails when running through QMC. I have checked 'allow run (unsafe) macro. Still no result. Any clue?

Version history
Revision #:
1 of 1
Last update:
‎06-20-2012 03:04 PM
Updated by: