Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
microwin88x
Contributor III

Load Data by Script with JSON

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

Tags (2)
1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Load Data by Script with JSON

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

18 Replies
vishsaggi
Esteemed Contributor III

Re: Load Data by Script with JSON

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
datalakeyu
Valued Contributor

Re: Load Data by Script with JSON

REST in Qlik sense will do it.

Frank_Hartmann
Honored Contributor II

Re: Load Data by Script with JSON

Also have a look here:

Extracting data from non nested Json arrays

Hope this helps

Frank_Hartmann
Honored Contributor II

Re: Load Data by Script with JSON

see attached. hope this helps

Re: Load Data by Script with JSON

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

LOADRowNo() 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_sommerstevedarkmichael.hlava

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Frank_Hartmann
Honored Contributor II

Re: Load Data by Script with JSON

Hi Anil,

you are absolutely right.

please have a look at the attached sample.

Frank_Hartmann
Honored Contributor II

Re: Load Data by Script with JSON

A better solution might be seen in attached qvw

microwin88x
Contributor III

Re: Load Data by Script with JSON

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

1 - In my file I have the following 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"}]

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!