topic Re: JSON Conversion in QlikView Script in QlikView

JSON Conversion in QlikView Script

simon_koenen — Fri, 12 May 2017 17:39:23 GMT

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?

Re: JSON Conversion in QlikView Script

Anil_Babu_Samineni — Sat, 13 May 2017 01:33:32 GMT

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

Re: JSON Conversion in QlikView Script

RSvebeck — Sat, 13 May 2017 08:20:41 GMT

Check out this thread:

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

Robert

Re: JSON Conversion in QlikView Script

Frank_Hartmann — Sat, 13 May 2017 11:30:09 GMT

also have a look here:

Load Data by Script with JSON

Re: JSON Conversion in QlikView Script

simon_koenen — Mon, 15 May 2017 09:26:26 GMT

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.

Re: JSON Conversion in QlikView Script

RSvebeck — Tue, 16 May 2017 19:00:01 GMT

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