Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator 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!!!

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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

View solution in original post

22 Replies
vishsaggi
Champion III
Champion III

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

JSON Loader

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

Anil_Babu_Samineni

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

REST in Qlik sense will do it.

Frank_Hartmann
Master II
Master II

Also have a look here:

Extracting data from non nested Json arrays

Hope this helps

Frank_Hartmann
Master II
Master II

see attached. hope this helps

Anil_Babu_Samineni

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
Master II
Master II

Hi Anil,

you are absolutely right.

please have a look at the attached sample.

Frank_Hartmann
Master II
Master II

A better solution might be seen in attached qvw

microwin88x
Creator III
Creator III
Author

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!