Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

JSON Conversion in QlikView Script

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?

5 Replies
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Specialist
Specialist

Check out this thread:


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


Robert

Svebeck Consulting AB
Highlighted
Master II
Master II

also have a look here:

Load Data by Script with JSON

Highlighted
Partner
Partner

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.

Highlighted
Specialist
Specialist

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...



Svebeck Consulting AB