topic Re: JSON how to load the second hierarchical level (local) in QlikView

JSON how to load the second hierarchical level (local)

cdss-developer — Sat, 16 Nov 2024 19:52:05 GMT

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

Re: JSON how to load the second hierarchical level (local)

Frank_Hartmann — Fri, 18 Oct 2019 11:30:42 GMT

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

Re: JSON how to load the second hierarchical level (local)

cdss-developer — Mon, 21 Oct 2019 06:33:38 GMT

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

Re: JSON how to load the second hierarchical level (local)

Frank_Hartmann — Mon, 21 Oct 2019 08:58:03 GMT

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.

Re: JSON how to load the second hierarchical level (local)

cdss-developer — Mon, 21 Oct 2019 14:15:35 GMT

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

Re: JSON how to load the second hierarchical level (local)

Brett_Bleess — Wed, 23 Oct 2019 20:00:05 GMT

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/Handling-other-generic-commands.htm

Only other thing I could find for you.

Regards,
Brett

Re: JSON how to load the second hierarchical level (local)

deep — Wed, 24 Jun 2020 14:26:11 GMT

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

Re: JSON how to load the second hierarchical level (local)

deep — Thu, 25 Jun 2020 07:50:15 GMT

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...

Re: JSON how to load the second hierarchical level (local)

deep — Fri, 26 Jun 2020 14:14:34 GMT

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