topic Loading data from multiple JSON files in App Development

Loading data from multiple JSON files

ms_12 — Fri, 04 Oct 2024 12:38:29 GMT

Hi All, I have sample JSON structure as shown below.

{ "policy1": [ { "date": "10/01/2024", "name": "ABC", "id": "a101b", "value": 120 }, { "date": "15/01/2024", "name": "XYX", "id": "a111b", "value": 110 } ], "policy2": [ { "date": "11/01/2024", "name": "PQR", "id": "a131b", "value": 100 }, { "date": "19/01/2024", "name": "WXY", "id": "a221b", "value": 150 } ] }

I would like to load data from above json file. In my case JSON files are stored in shared folder location. I just referred to the post below to get started which helped me to understand loading JSON data.

https://community.qlik.com/t5/Member-Articles/How-to-read-json-files-with-Qlik-Sense/ta-p/2120598

I was able to load data using below. But I am struggling to load the data for multiple root within json.

LOAD *
FROM [lib://SharedFolder/test_jan-2024.json]
(json, Table is 'Root/policy1');

Also, I have multiple JSON files so I tried below but it's giving me error

LOAD *
FROM [lib://SharedFolder/test_*.json]
(json, Table is 'Root/policy1');

How do I load multiple JSON files?

Re: Loading data from multiple JSON files

Kushal_Chawda — Fri, 04 Oct 2024 13:31:56 GMT

@ms_12 try below

// loop through all JSON files for each vFile in FileList('lib://SharedFolder/test_*.json') root: LOAD * FROM [$(vFile)] (json, Table is 'Root'); Data: Load * Inline [ Junk ]; let i =1; // loop through all json root values do while i <= NoOfFields('root') let vFieldName = FieldName($(i),'root'); // load fields within each root Concatenate(Data) LOAD *, '$(vFieldName)' as Policy_Name FROM [$(vFile)] (json, Table is 'Root/$(vFieldName)'); let vFieldAvailable = FieldName(FieldNumber('$(vFieldName)','Data'),'Data'); if Len('$(vFieldAvailable)') >0 then drop field [$(vFieldName)] From Data; Endif let i=i+1; loop Drop Table root; Next Drop Field Junk;

Re: Loading data from multiple JSON files

ms_12 — Fri, 04 Oct 2024 14:38:52 GMT

That worked. Thank you.