Qlik Community

Qlik Sense App Development

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

rohitk1609
Valued Contributor II

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
Honored Contributor II

Re: How to load local JSON file into Qlik Sense

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

5 Replies
Highlighted
Partner
Partner

Re: How to load local JSON file into Qlik Sense

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
Valued Contributor II

Re: How to load local JSON file into Qlik Sense

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

 

Thanks,

Rohit 

Frank_Hartmann
Honored Contributor II

Re: How to load local JSON file into Qlik Sense

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
Valued Contributor II

Re: How to load local JSON file into Qlik Sense

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

Frank_Hartmann
Honored Contributor II

Re: How to load local JSON file into Qlik Sense

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