topic Re: how to load JSON file data in to qlikview in QlikView

how to load JSON file data in to qlikview

Kumar6872 — Sat, 16 Nov 2024 19:27:34 GMT

Hello All,

We have data that come in JSON file ( ABC.json) with following info. Need help as how to load the data in qlikview.

[ {

"ID" : "1234",

"Details" : "Product A"

} , {

"ID" : "1222",

"Details" : "Product B"

} ]

Re: how to load JSON file data in to qlikview

Taoufiq_Zarra — Sat, 14 Dec 2019 19:15:15 GMT

Hi,

I suggest you see these solutions:

Solution1

Solution2

personally I prefer to convert the Json file to CSV and work with CSV.

Cheers,

Re: how to load JSON file data in to qlikview

Brett_Bleess — Thu, 19 Dec 2019 20:51:00 GMT

Did the post you received help with your use case? If so, be sure you return to the thread and use the Accept as Solution button on that post to give the poster credit for the assistance and let other Community Members know it worked. If you are still looking for further guidance, leave an update.

Regards,
Brett

Re: how to load JSON file data in to qlikview

Kumar6872 — Thu, 19 Dec 2019 20:52:29 GMT

But does QLikview officially support JSON?

Re: how to load JSON file data in to qlikview

Brett_Bleess — Mon, 23 Dec 2019 14:00:53 GMT

I see nothing in the Help regarding JSON connectors, so the best I can offer at this point would be for you to reach out to your account manager to see if they can provide information regarding how customers are addressing things, and here is the Connector Help link in case you want to check yourself:

https://help.qlik.com/en-US/connectors/Content/Connectors_Home/Home.htm#J

Regards,
Brett

Re: how to load JSON file data in to qlikview

Kumar6872 — Thu, 26 Dec 2019 19:07:11 GMT

We are the first people who have been asked to use JSON files as inputs for the Qlikview.

Does QLikview officially supports loading JSON files to qlikview? I am also having issues with the date feilds not read correctly by Qlikview.

I am using the following code to load the JSON files but when the JSON file size is more than 400 to 500 MB then scripts failed to load.

[..\..\Projects\dailysales.json]
(json, utf8, embedded labels, no quotes, no eof);

Re: how to load JSON file data in to qlikview

Taoufiq_Zarra — Thu, 26 Dec 2019 19:08:51 GMT

can you share the code ?

Re: how to load JSON file data in to qlikview

Darren_Ball — Thu, 02 Jan 2020 11:38:23 GMT

Hi,

Further to Brett's response, you may be able to use the Premium Qlik General Web Connector, which can be found as part of the Qlik Web Connector's package.

Here you are able to consume JSON which could be from an API response or a local file on your machine.

From the General Web Connector's tables, select something like JSONToXMLRaw (recommended), which can be consumed by Qlik or JSONToTable, where the connector will attempt to convert the JSON to a table, which again can be consumed by Qlik.

Click 'Parameters' to configure the connector for this table and in the case of a local JSON file select FILE from the 'Request Method' list:

(If the JSON is a response from a Rest API request, then you'd set this to 'GET')

In the 'Request URI' box underneath, enter the file's path and name:

(In the case of using an API, this will be the API call)

In reading a local file, you will need to give the Qlik Web Connectors permission to read files. This can be done by editing the deploy.config file in something like Notepad and making the appropriate change. If you search for <FileSystemAccessAllowedPaths>" in the config file you'll see where to make the change and some examples.

Clicking the green 'save and run table' at the bottom will process the request and you'll see a preview to the right:

You can then use the Tabs along the top to get the relevant script to place into your Qlik app, which when run will pull the data into it.

You can get the Qlik Web Connectors from the Qlik download site. As this is a Premium connector, you'll need to contact your local Qlik office or partner to get a relevant Licence file with this connector enabled.

I hope this helps to clarify how to use the Qlik Web Connectors and JSON data together.

Darren

Re: how to load JSON file data in to qlikview

Kumar6872 — Thu, 02 Jan 2020 16:35:08 GMT

Thanks, is it for Qlikview or QlikSense..

I am using Qlikview

Re: how to load JSON file data in to qlikview

Frank_Hartmann — Thu, 02 Jan 2020 17:14:05 GMT

hi kumar,
nativly Qlikview does not support consuming json files.
Either you will have to setup the qlik rest connector (but the jsonfile has to present as a webfile)
or you can use the Premium Qlik General Web Connector as Darren mentioned, or you can go with the Dummy-solution Taoufiq proposed and make use of the jscript macro to extract the json file locally. Another option would be to start a small python or powershell script inside scriptmodule which is extracting the json data
and saving the output to a predefined folder which then can be used for further loading in qlikscript!

hope this helps.

Re: how to load JSON file data in to qlikview

Brett_Bleess — Thu, 02 Jan 2020 17:18:22 GMT

Here is the Help link to the General Web Connector Darren mentioned too, but please note this connector requires a license, you would need to contact your account manager about that in order to be able to use this connection.

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/General-Web-Connector.htm

Regards,
Brett

Re: how to load JSON file data in to qlikview

Darren_Ball — Fri, 03 Jan 2020 09:40:38 GMT

The Qik Web Connectors is a separately installed Qlik product that is a web service "listening out" for Qlik requests, as such it works with both QlikView and Qlik Sense.