JSON how to load the second hierarchical level (local)

[cdss-developer]

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.

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

Greetings,
Eelco

[Frank_Hartmann]

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!

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]

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]

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]

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]

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

[deep]

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]

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]

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