topic Re: Load Data by Script with JSON in QlikView

Load Data by Script with JSON

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a file with the following data with JSON format:

[{"id":"500","program":"prog003","number":"333525","datetime":"2017-01-03 15:45:15","zone":"north","amount":"100"}, {"id":"501","program":"prog003","number":"333526","datetime":"2017-01-03 15:48:30","zone":"north","amount":"300"}, {"id":"502","program":"prog003","number":"333527","datetime":"2017-01-03 17:25:23","zone":"south","amount":"150"}]

Do you know how can I load the data in a table by script? (Preferably with no connectors)

Thank you!!!

Re: Load Data by Script with JSON

vishsaggi — Sat, 18 Mar 2017 00:50:17 GMT

Look here let us know if they are of any help:

JSON Loader

https://community.qlik.com/thread/45548

Re: Load Data by Script with JSON

Anil_Babu_Samineni — Sat, 18 Mar 2017 01:57:48 GMT

The Best way, as i assume. If, The JSON file stored in URL then just install REST Connection instead of SOAP Driver. This will helps you and Qlik has good capture to deserve it.

Good Luck

Re: Load Data by Script with JSON

Anonymous — Sat, 18 Mar 2017 01:58:26 GMT

REST in Qlik sense will do it.

Re: Load Data by Script with JSON

Frank_Hartmann — Sat, 18 Mar 2017 08:30:34 GMT

Also have a look here:

Extracting data from non nested Json arrays

Hope this helps

Re: Load Data by Script with JSON

Frank_Hartmann — Sat, 18 Mar 2017 17:26:22 GMT

see attached. hope this helps

Re: Load Data by Script with JSON

Anil_Babu_Samineni — Sun, 19 Mar 2017 03:33:47 GMT

Looks good, If you have thoughts on this. You may work on Loop or For each to get dynamic field Names.

LOAD	RowNo() as Row,id From_Field (Tmp, data_csv);

Every time, We can't write this. In fact, JSON is the huge DB it could be Bull / Vast fields. So then, We can't write for them. Work on that and let us know. May be this is simple for < 50 Fields(Just Thinking)

marcus_sommer‌ stevedark‌ michael.hlava‌

Re: Load Data by Script with JSON

Frank_Hartmann — Sun, 19 Mar 2017 09:53:49 GMT

Hi Anil,

you are absolutely right.

please have a look at the attached sample.

Re: Load Data by Script with JSON

Frank_Hartmann — Sun, 19 Mar 2017 12:36:08 GMT

A better solution might be seen in attached qvw

Re: Load Data by Script with JSON

microwin88x — Sun, 19 Mar 2017 20:41:32 GMT

Hello Frank, thanks for your solution. A few questions:

1 - In my file I have the following format:

But you add {"test": at the beggining and a curly bracket } at the end:

{"test": [{"id":"500","program":"prog003","number":"333525","datetime":"2017-01-03 15:45:15","zone":"north","amount":"100"}, {"id":"501","program":"prog003","number":"333526","datetime":"2017-01-03 15:48:30","zone":"north","amount":"300"}, {"id":"502","program":"prog003","number":"333527","datetime":"2017-01-03 17:25:23","zone":"south","amount":"150"}]}

> Is it possible not to add them? Or maybe add it on script when I load the data?

2 - As this is a sample file, I have many of thems and I'd need to read them all. They have the following format:

data_fin_500.json (this is the first file - 500 is the first id)

data_fin_503.json (second file - 503 would be the next id to read)

data_fin_506.json (third file - 506 would be the next id to read)

And so on. I'd need to read all files (could be more) but always with 3 id's on every file.

Do you know how could I do that?

Thanks!

Re: Load Data by Script with JSON

Frank_Hartmann — Mon, 20 Mar 2017 08:37:08 GMT

1. As per my knowledge this is not possible. The JS macro needs to know the information about the tablename, which was missing in your case.

2. You could create an Inlinetable in which you load all the different data_fin.json files by creating a loop.

hope this helps

Re: Load Data by Script with JSON

Frank_Hartmann — Mon, 27 Mar 2017 18:34:08 GMT

Hi,

I think i found a workaround.

just add an arrayname in maintable and use your json2csv function in Resident Load.

see attached sample.

hope this helps

Re: Load Data by Script with JSON

Anonymous — Wed, 26 Jul 2017 03:34:34 GMT

@franky_h79

1. What is the purpose of adding DummyField?

2. Where and How is the Jscript for json2csv function loaded?

Thanks for guiding.

Re: Load Data by Script with JSON

Frank_Hartmann — Wed, 26 Jul 2017 06:26:46 GMT

1. In order to get this macro work without having an arrayname in your json file you will have to generate this

missing arrayname. in this case i just named it DummyField. If you have an arrayname in your json file then you dont have to use the preload and the name "DummyField" but your arrayname like:

json2csv(@1,'PutYourArrayNameHere') as data_csv

2. Look inside the module (Tools-->Edit Module). there you will find the jscript function which is called inside QV script. Make sure that the settings in your module on the left side are correct

hope this helps

Re: Load Data by Script with JSON

Anonymous — Fri, 24 Aug 2018 10:29:28 GMT

Hi Frank,

thanks for your solution with the Json_253827_Loop.qvw, but i have problem to load my Json file.

Can you help me to load this JSON file?

The format is:

{

"shocks": [

{

"timestamp": 1529278573,

"truckNr": "FN539599",

"shipToPartyNr": "59179278",

"intensityHorizontal": 484,

"intensityVertical": 132,

"shockLevel": 2,

"truckReaction": "show_on_display"

{

"timestamp": 1529278684,

"truckNr": "FN539599",

"shipToPartyNr": "59179278",

"intensityHorizontal": 209,

"intensityVertical": 55,

"shockLevel": 1,

"truckReaction": "save"

}

]

}

Thanks!

Re: Load Data by Script with JSON

Frank_Hartmann — Fri, 24 Aug 2018 16:45:36 GMT

Hi Niels,

see attached file.

NOTE: You will have to remove the linebreaks in your json file.

Notepad++ --> Edit -> Line Operations -> Join Lines

or you will have to join, concat the lines on QV script level what might be a little bit tricky.

hope this helps

Re: Load Data by Script with JSON

Anonymous — Tue, 28 Aug 2018 12:09:48 GMT

Thanks for your help!

Re: Load Data by Script with JSON

ascioneanna — Mon, 28 Jan 2019 14:46:35 GMT

Hi,

can you help me with this question?

https://community.qlik.com/t5/New-to-Qlik-Sense/LOAD-ON-SENSE-FILE-XLSX-WITH-JASON-COLUMN/m-p/1535960#M124893

Re: Load Data by Script with JSON

ascioneanna — Mon, 28 Jan 2019 14:46:52 GMT

Hi,

can you help me with this question?

https://community.qlik.com/t5/New-to-Qlik-Sense/LOAD-ON-SENSE-FILE-XLSX-WITH-JASON-COLUMN/m-p/1535960#M124893

Re: Load Data by Script with JSON

AAbarzua — Mon, 30 Aug 2021 13:31:04 GMT

Hola Frank, me podrías ayudar con esto. Tengo un json que la solución planteada no me procesa bien la información porque tengo un campo con subcampos? te envió json, me imagino que la solución va por una modificación en el script del modulo de QlikView, lo cual desconozco como se realiza. Desde ya mucha graxx Te adjunto json. Estare atento