

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Json to Qlik - How to load it when the Json is like that?
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?
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this could be helpful:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Frank,
I've red that thread and it's not the same case, unfortunatelly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use Powershell or Python to convert json to csv.
For example with a powershell script:
param
(
[Parameter(Mandatory=$true)][string]$JsonFile
)
$jf=Get-ChildItem -Path $JsonFile
if($jf.Exists){
$obj=(Get-Content $jf.FullName -Encoding UTF8) -join "`n" | ConvertFrom-Json
$cols=$obj.ResultSet.Tables[0].Columns
$rows=$obj.ResultSet.Tables[0].Rows
$csv=@()
$s=""
for($i=0;$i -lt $cols.length;$i++){
$s+=',"'+$cols[$i].ColumnName +'"'
}
$csv+=$s.TrimStart(",")
for($i=0;$i -lt $rows.length;$i++){
$s=""
for($j=0;$j -lt $cols.length;$j++){
$s+=',"'+ $rows[$i].Cells[$j] + '"'
}
$csv+=$s.TrimStart(",")
}
Set-Content -Path ($jf.DirectoryName+"\"+$jf.BaseName+".csv") -Value $csv -Encoding UTF8 -Force
}
Call:
powershell c:\temp\json2csv.ps1 -JsonFile c:\temp\ResultSet.json
- Christian

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Christian,
That's a nice option, even though my skills on python are lacking at this momment.
Do you think that use some 'middle' is the only option?
BR,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Bala,
I'll read it for sure, thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Christian,
nice script. works fine 🙂
do you have the same for python?
regards franky

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, i was not aware that it is a nested json file. my solution only works for non nested json files.
But have a look into Christians solution which works very well. But be aware that you will have to run powershell 3.0 on your machine!
hope this helps

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Frank,
as python:
import os,json,csv
from argparse import ArgumentParser
parser=ArgumentParser()
parser.add_argument("-jf","--jsonfile",dest="jf")
args=parser.parse_args()
if not args.jf is None and os.path.isfile(args.jf):
print("Ok!")
with open(args.jf,mode="r",encoding="utf-8") as f: obj=json.load(f)
cols=obj['ResultSet']['Tables'][0]['Columns']
rows=obj['ResultSet']['Tables'][0]['Rows']
header=[]
for col in cols: header.append(col['ColumnName'])
with open(os.path.splitext(args.jf)[0]+".csv",mode="w",encoding="utf-8") as f:
writer =csv.writer(f,delimiter=",",quotechar='"',quoting=csv.QUOTE_ALL,lineterminator='\n')
writer.writerow(header)
for row in rows: writer.writerow(row['Cells'])
Call:
python.exe c:\temp\json2csv.py -jf c:\Temp\ResultSet.json
- Christian

- « Previous Replies
-
- 1
- 2
- Next Replies »