JSON Conversion in QlikView Script

[simon_koenen]

Hi,

I have a table containing multidimensional JSON data within one of the attributes. Is there any example how to convert the json to sub-tables without using extensions or macros but using the normal script functionality?

The JSON looks like this:

[{ "Id" : "1", "User" : "Mustermann", "Location" : [{ "PostalCode" : "33813", "City" : "Berlin"}], "Test" : [{ "Name" : "Optical", "Result" : "ok" },{ "Name" : "Pressure", "Result" : "not ok" }] }, { ... etc. ... }]

I want to have one table containing the user information and one additional with the tests linked over the user id. Furthermore it should be dynamical because the sub table information could differ (sometimes they contain other tests or more fields).

Any suggestions / examples?

[Anil_Babu_Samineni]

Can i know which format of file is this?

May be this?

Tmp:

Load json2csv(@1,'test') as data_csv

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Tmp2:

LOAD RowNo() as Row,id From_Field (Tmp, data_csv);

[RSvebeck]

Check out this thread:

https://community.qlik.com/thread/150233?forceNoRedirect=true

Robert

[Frank_Hartmann]

also have a look here:

Load Data by Script with JSON

[simon_koenen]

Hi,

I am afraid the posted links do not help me because they are using macros and/or additional connectors but I am trying to find a solution with the QlikView basic functionality. Like e.g. subs and loops.

The data is loaded from a MongoDB.

[RSvebeck]

I once started thinking about writing a script (before the REST connector came) to load JSON data with normal script code, using subfield() and HierarchyBelongsTo() etc.

But then the connector arrived

The syntax of JSON can become quite complex with sub on sub levels, but you should be able to do it...