Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
thank you very much 🙂
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.