Json to Qlik - How to load it when the Json is like that?

[rafaelvianna]

Hi guys!

After reading the session about loading the Json to Qlik I couldn't find a way to generate the data the way I wish with the json that is given to me. I hope someone have an idea (or similiar problem solution) to help me.

I have a result from a Json that's like this:

{

  "ResultSet": {"Tables": [  {

      "Columns":      [

                  {

            "ColumnName": "UserId",

            "DataType": "System.Int32"

        },

                  {

            "ColumnName": "Start date",

            "DataType": "System.DateTime"

        },

                  {

            "ColumnName": "End date",

            "DataType": "System.DateTime"

        },

                  {

            "ColumnName": "Duration",

            "DataType": "System.TimeSpan"

        }

      ],

      "Rows":      [

        {"Cells":          [

            9000,

            "2018-08-30T09:56:59.953",

            "2018-08-30T09:57:00.237",

            284

        ]},

        {"Cells":          [

            9000,

            "2018-08-30T09:57:00.237",

            "2018-08-30T09:58:00.937",

            60700

        ]},

        {"Cells":          [

            9000,

            "2018-08-30T09:58:00.937",

            "2018-08-30T10:26:01.67",

            1680733

        ]}

      ],

      "TableName": "Result table",

      "Hide": null,

      "Delete": false

  }]},

  "ShowReport": true,

  "invalidCharInXml": "\u001f"

}

As you can see, first I have my 'column names' and after I have the values. Usually, it doesn't come like this, but this specific code does, and I can't change it. I have to deal with the results.

And I'm trying to load it on QV like a regular excel file columns. In this sample, it would be like:

UserID     Start Date                             End Date                              Duration

9000        2018-08-30T09:56:59.953   2018-08-30T09:58:00.937     284

9000        2018-08-30T09:57:00.237   2018-08-30T09:58:00.937     60700

...

But I just can't do that. Anyone have any idea of how to deal with that?

[Frank_Hartmann]

thank you very much 🙂

[rafaelvianna · Accepted solution]

Hi Everyone!

Thanks a lot for your answers.

A friend of mine (smarter than me) solved the problem, I guess. Let me set the solution here:

Something I've forgot to tell: it comes from a REST and not a file.

LIB CONNECT TO 'test (REST CONNECTION NAME)';

RestConnectorMasterTable:

SQL SELECT

"__KEY_ResultSet",

(SELECT

"__KEY_Tables",

"__FK_Tables",

(SELECT

"ColumnName",

"DataType",

"__FK_Columns"

FROM "Columns" FK "__FK_Columns"),

(SELECT

"__KEY_Rows",

"__FK_Rows",

(SELECT

"@Value",

"__FK_Cells"

FROM "Cells" FK "__FK_Cells" ArrayValueAlias "@Value")

FROM "Rows" PK "__KEY_Rows" FK "__FK_Rows")

FROM "Tables" PK "__KEY_Tables" FK "__FK_Tables")

FROM JSON (wrap off) "ResultSet" PK "__KEY_ResultSet";

[Columns]:

LOAD [ColumnName] AS [ColumnName],

    [DataType] AS [DataType],

    [__FK_Columns] AS [__KEY_Tables],

        RecNo() as NUM

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Columns]);

[Cells]:

LOAD AutoNumber(RecNo(),[__FK_Cells]) as NUM,

[@Value],

    [__FK_Cells] AS [__KEY_Rows]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Cells]);

[Rows]:

LOAD [__KEY_Rows] AS [__KEY_Rows],

[__FK_Rows] AS [__KEY_Tables]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Rows]);

[Tables]:

LOAD [__KEY_Tables] AS [__KEY_Tables],

[__FK_Tables] AS [__KEY_ResultSet]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Tables]);

Columns2Cells:

LOAD 0 as Dummy

AutoGenerate 0;

Join(Columns2Cells)

LOAD NUM, [@Value], [__KEY_Rows] as Rows

RESIDENT Cells;

Join(Columns2Cells)

LOAD ColumnName, NUM

RESIDENT Columns;

DROP Field Dummy;

[TablesX]:

LOAD RecNo() as Key,

ColumnName as ColName,

NUM as ColId,

[@Value] as Val,

Rows

RESIDENT Columns2Cells;

FinalTable:

LOAD 0 as Dummy

AutoGenerate 0;

FOR i = 1 to FieldValueCount('ColName')

  LET vField = FieldValue('ColName', $(i));

  LET vField1 = '[' & FieldValue('ColName', $(i)) & ']';

  Join(FinalTable)

  LOAD Rows,

  Val as $(vField1)

  Resident TablesX

  Where ColName = '$(vField)';

NEXT

DROP Field Dummy;

DROP TABLE TablesX;

DROP TABLE Columns2Cells;

DROP TABLE Tables;

DROP TABLE Rows;

DROP TABLE Cells;

DROP TABLE Columns;

DROP TABLE RestConnectorMasterTable;

Feel Free to suggest any improvement you find.