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.

Extracting data from non nested Json arrays

Frank_Hartmann
Honored Contributor

Extracting data from non nested Json arrays

Hello QV Community,

while searching for an solution to get the data of a not nested json-array loaded into qlikview I humbled over a blog

which describes exactly the way to get this done by JScript-macro. I think its worth to have a look about it.

I personally use this to load data from multiple arrays and connect them with a For Next Loop to an Inline Table and it

works very well!

I have attached a sample qvw + json File which shows how to get the data loaded into QV.

Tags (3)
Attachments
Comments
ed_hoecker
Contributor II

Hi,

I'm a newbee. I want to load data that are stored in a json file. If I start your example it works fine (also the script).

If I copy the script into a new analysis (qvw) the script is marked as not executable. I beleve the problem is json2csv

Have I anything to include in my new qvw file?

Thanks for your help,

Ed

Frank_Hartmann
Honored Contributor

Go to Tools --> Edit Module and paste following code into the Module:

'---------------------------------------------------------------

'-----------------------------------------------------------

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

}

'-----------------------------------------------------------

'-----------------------------------------------------------

also activate the Script Engine for JScript on the left side of the Module Window

Unbenannt.png

Then go to your Script and have alook if the error persists. But please have in mind that you will have to adapt the arrayname according to your json file. Its concerning this line (--> 'responseTime') :

json2csv(@1, 'responseTime') as responsetime_csv,

hope this helps

ed_hoecker
Contributor II

Thanks for your support but it doesn't work. I've added the code in the module, I have activated JAVA script, I have set the policies and a have copied the code from the working exsample in my qvw file. The function json2csv is not executable.

ed_hoecker
Contributor II

Ups,

just now I've seen that the flag JAVA SCRIPT wasn't set. I don't know why. I've set it again, I've stored my file and now it looks fine.

Now I'm able to analyse the script and hopefully I can change it to my JSON file specification soon.

Thanks!

ancuta_barbu
New Contributor

Hi,

These days I discovered it can also be achieved by installing REST connector in QlikView and then using the the script generated by Qlik Sense with the REST Connector (in the Qlik Sense it comes installed by default).

It works with nested arrays too.

Cheers,

Anca

Version history
Revision #:
1 of 1
Last update:
‎06-26-2016 03:26 PM
Updated by: