Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cdss-developer
Contributor III
Contributor III

JSON how to load the second hierarchical level (local)

Hello all,

I'm trying to load a local JSON file in qlikview. I have already managed to load specific 'tables'  from a JSON file by using the following code:

Test1:
LOAD * FROM [.\test7.json] (json, Table is [./TEST1], codepage is 1252, embedded labels);

Test2:
LOAD * FROM [.\test7.json] (json, Table is [./TEST2], codepage is 1252, embedded labels);

But I also want to load data from 'details'  table, which is a level lower then ' TEST1'  (see attached zip file with json and qvw). I would expect that  I would be able to get this data by using something like: Table is [./TEST1/details], but that doesn't seem to work.

JSON second hierarchical level.png

I really need your expertise. Who can help me solve this problem?

Greetings,
Eelco

Labels (4)
8 Replies
Frank_Hartmann
Master II
Master II

have a look at the attached zip file. it contains a python script for flattening your json file and transform it to csv. the transformed csv can be loaded just as normal into QV. In order to generate the csv file within the script, i inserted an execute statement  at the beginning, which triggers the python script!

Unbenannt.png

So in order to get it working you will need to install Python and after that, through cmd-shell, Pandas and Cherrypicker (You will find installation instructions on youtube)

 

hope this helps

cdss-developer
Contributor III
Contributor III
Author

Hi Frank,

Thank you very much for your quick response, for taking the time to look at my script, work it out and describe it so clearly. This outcome is exactly what I need. 

Do I understand correctly, that the data cannot be extracted using/ modifying a QV script?

Unfortunately it is not (easy) possible to install new programs/ tools (like Python) on my workstation.

Alternatively, a VB script in Qlikview often works well in these kinds of cases. Do you know if it is possible to convert this Python script to a VB script, which I can run in Qlikview?

Greetings, 
Eelco

Frank_Hartmann
Master II
Master II

As far as i know there are jscript macros for non-nested json arrays. But for nested Json Files i havent found one. There might be also script solutions but they are always static and if there is a little difference between old an new json file you will probably have to to rewrite/adapt your script. So the Python solution is probably the solution because its handy, dynamic and very fast to setup.

Btw: Maybe place your jsonfiles to a weblocation (Dropbox, etc..) and use the qlik rest connector to extract the json file. thats the only possibility to extract the data without external tools.

cdss-developer
Contributor III
Contributor III
Author

Thank you!

Yes, I am (preferably) looking for a dynamic solution. It should indeed work via a web connection, but in this case it must be a secure web storage. That's why I'm currently working on whether I can connect to my OneDrive company account (which sadly seems to be blocked for QlikView access at the moment). If this doesn't work, I'm gonna try the Python solution.
In the meantime, if there is someone else with a other solution, please let me know.
Greetings,
Eelco

Brett_Bleess
Support (Former)
Support (Former)

Eelco, Frank likely has the best solution, but I did find the following link, going to let you have a look as I honestly do not know if this will get you anywhere or not, I believe you would have to basically code your own connector in this case...

https://help.qlik.com/en-US/qlikview-developer/April2019/Subsystems/QVXSDKAPI/Content/QV_QVXSDKAPI/H...

Only other thing I could find for you.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
deep
Contributor II
Contributor II

hi there, 

after executing this code am getting this error, could you please help me to fix 

EXECUTE statement requires Script (Allow Database Write and Execute Statements) in User Preferences settings
Execute cmd /C "cd C:\Users\admin\Desktop\json_example\ && flatten_jsonfile_with_pandas_and_cherrypicker.py"

 

Thanks,

deep

deep
Contributor II
Contributor II

hi there,

I am getting this error could you help me, please.

error:

File "flatten_jsonfile_with_pandas_and_cherrypicker.py", line 2, in <module>
from cherrypicker import CherryPicker
ModuleNotFoundError: No module named 'cherrypicker'

and when am installing (pip install cherrypicker) it is giving me this

error:

FileNotFoundError: [Errno 2] No such file or directory: 'requirements-dev.txt'
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

please help me to fix this one... 

deep
Contributor II
Contributor II

hi @Frank_Hartmann 

I am getting this error could you help me, please.

error:

File "flatten_jsonfile_with_pandas_and_cherrypicker.py", line 2, in <module>
from cherrypicker import CherryPicker
ModuleNotFoundError: No module named 'cherrypicker'

and when am installing (pip install cherrypicker) it is giving me this

error:

FileNotFoundError: [Errno 2] No such file or directory: 'requirements-dev.txt'
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

please help me to fix this one... 

please help me...

FYI: my JSON files is like this

{
"title": "",
"description": "",
"position": 10,
"question_count": 1,
"id": "1234",
"href": "https://something.something",
"questions": [
{
"id": "123",
"position": 1,
"visible": true,
"family": "single",
"subtype": "ver",
"sorting": null,
"required": {
"text": "",
"type": "all",
"amount": "50"
},
"validation": null,
"forced_ranking": false,
"headings": [
{
"heading": "In process"
}
],
"href": "https://something",
"answers": {
"choices": [
{
"position": 1,
"visible": true,
"text": "50%",
"quiz_options": {
"score": 0
},
"id": "316"
},
{
"position": 4,
"visible": true,
"text": "51% - 60%",
"quiz_options": {
"score": 0
},
"id": "107"
},
{
"position": 3,
"visible": true,
"text": "61% - 70%",
"quiz_options": {
"score": 0
},
"id": "108"
},
{
"position": 4,
"visible": true,
"text": "71% - 80%",
"quiz_options": {
"score": 0
},
"id": "109"
},
{
"position": 5,
"visible": true,
"text": "81% - 90%",
"quiz_options": {
"score": 0
},
"id": "110"
},
{
"position": 6,
"visible": true,
"text": "91% - 100%",
"quiz_options": {
"score": 0
},
"id": "111"
}
]
}
}
]
},

thanks,

deep