Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Specialist III
Specialist III

How to load local JSON file into Qlik Sense

Hi techies,

I have a use case where I need to load json data into Qlik. my json file is locally stored.

I am sharing the json data below because I can't load json file here.

please copy and paste below data in note pad and save it with .json format.

{ "CountLogs": [ { "Counts": [ { "Name": "Line 1", "RegisterId": 0, "Value": 121 }, { "Name": "Line 2", "RegisterId": 1, "Value": 127 }, { "Name": "Zone 1", "RegisterId": 2, "Value": 156 }, { "Name": "ABC", "RegisterId": 3, "Value": 382 }, { "Name": "LineExclude", "RegisterId": 4, "Value": 125 } ], "LogEntryId": 353493, "Timestamp": "2018-12-03T14:21:00Z" } ], "FriendlyDeviceSerial": "20025" }

Thanks, Rohit 

Labels (1)
1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

1. Enable Legacy Mode for Sense ( in order to be able to execute EXECUTE statements in QS Script)

-->    Open C:\Users\{user}\My Documents\Qlik\Sense\Settings.ini in a text editor (normally the File is empty)

Change StandardReload=1 to StandardReload=0.

2. Install Python and Pandas (https://www.youtube.com/watch?v=Iqjy9UqKKuo)

3. Place the attached Python script into an folder and add following script line as the beginning of you script and adapt the path where you saved the test123.py!

 

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"

 

 this will generate an Output.csv to the folder in which you placed the python script. You will have to place the data.json (if your json file is named different you will have to adapt line 5 in python script) also to this folder.

After that you can load the Output.csv in Qliksense which will give you your desired result!

Your script should look like this:

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"; //This will generate the Output.csv which is loaded in the next step


Data:
LOAD
    LogEntryId,
    "Timestamp",
    Name,
    RegisterId,
    Value
FROM [lib://Stamm/Output.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Result:

Unbenannt.png

If you have further questions feel free to ask!

hope this helps

View solution in original post

7 Replies
Vegar
Partner
Partner

Sorry, you can not intepret the JSON file using Qlik Sense scripting. Qlik Sense does not come with shipped with a JSON parser, you will need to use the Qlik REST Connector.

In order for Qlik Sense to read your file you could try to host the file in a web environment where you can access it using the Qlik REST connector. 

 

Good luck

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rohitk1609
Specialist III
Specialist III
Author

Can you tell me please how to host a local Json file in web environment ?

 

Thanks,

Rohit 

Frank_Hartmann
Master II
Master II

you can use python to do this.

If this is ok for you just respond and i try to help you setting up the script and the prerequisites!

rohitk1609
Specialist III
Specialist III
Author

Please help me to setup the script which we can use in Qlik Sense and load data from json local file

Frank_Hartmann
Master II
Master II

1. Enable Legacy Mode for Sense ( in order to be able to execute EXECUTE statements in QS Script)

-->    Open C:\Users\{user}\My Documents\Qlik\Sense\Settings.ini in a text editor (normally the File is empty)

Change StandardReload=1 to StandardReload=0.

2. Install Python and Pandas (https://www.youtube.com/watch?v=Iqjy9UqKKuo)

3. Place the attached Python script into an folder and add following script line as the beginning of you script and adapt the path where you saved the test123.py!

 

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"

 

 this will generate an Output.csv to the folder in which you placed the python script. You will have to place the data.json (if your json file is named different you will have to adapt line 5 in python script) also to this folder.

After that you can load the Output.csv in Qliksense which will give you your desired result!

Your script should look like this:

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"; //This will generate the Output.csv which is loaded in the next step


Data:
LOAD
    LogEntryId,
    "Timestamp",
    Name,
    RegisterId,
    Value
FROM [lib://Stamm/Output.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Result:

Unbenannt.png

If you have further questions feel free to ask!

hope this helps

View solution in original post

rohitk1609
Specialist III
Specialist III
Author

Hi Frank,

 

Sorry for late reply. I got busy with other stuff. I tried your code and it works fine with attached JSON file. But when I tried with my real JSON file it fails. I think your written python code is not the generic one.

Can you please run your code on attached file?

I am failing to attach zip file on ticket so I move JSON code to text file.

Frank_Hartmann
Master II
Master II

You will need to use a different python script.

see attached files.

hope this helps!