Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jessiemabalhin
New Contributor II

Parsing JSON data with loop

Hi All,

I am fairly new to Qlik and have been using it for a few months now. The company I work for has some of its data migrated in new tables with some fields having a key-pair type thing which is quite challenging to work with. Here is a sample data for one of those fields.

{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","500","LOT ","1","496","0","0","","496"]]}


I am able to extract data out of it using the parseJSON() function in SQL server. For single records, it is quite simple.

However, I find it very challenging with multiple records for a certain zool/quote request such as the one below where a loop is needed to go through each one.

I also don’t know what function, if there is, for parsing JSON data in Qlik sense or if there is something simpler instore on Qlik already.

HeaderID

ID

ResponseValue

65934

133416

{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","405.82","0","0","","405.82"]]}

65934

133409

{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","2510","0","0","","2510"]]}

65934

133441

{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","750","0","0","","750"]]}

65934133422

{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","410","0","0","","410"]]}

For the sample data above, I am able to work it out through a loop but in sql server. I was after the Total amount.

ID          HeaderID        Data

16693    65934              405.82

16697    65934              2510

16701    65934              750

16702    65934              410

So, the main goal is to create a similar table on Qlik.

Any advise, suggestion or solution is greatly appreciated. Thanks!

Tags (2)
1 Solution

Accepted Solutions

Re: Parsing JSON data with loop

If the structure is consistent you could just use simple string-functions like:

= purgechar(subfield(subfield('{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","405.82","0","0","","405.82"]]}'

, ':', 3), '",', 11), '"][{}')

to pick your wanted value. If not you will need to extract these field as an extra table and picking the value from there and/or merging the tables with eachother or maybe just to asscociate them within the datamodel. This extracting could be also done with subfield() - if there is no third parameter used subfield() will work like a loop and split every part into a new record. Here some examples - also to while-loop which will do quite the same:

Re: Splitting values within a field into individual rows on the table

Re: Loading an Array field in qlikview

- Marcus

2 Replies

Re: Parsing JSON data with loop

If the structure is consistent you could just use simple string-functions like:

= purgechar(subfield(subfield('{"COLUMNS":["ID","TableID","SubTableID","Quantity","UOM","QtyUnit","Amount","Freight","Setup","Turnaround","Total"],"DATA":[["1","1","1","4000","LOT ","1","405.82","0","0","","405.82"]]}'

, ':', 3), '",', 11), '"][{}')

to pick your wanted value. If not you will need to extract these field as an extra table and picking the value from there and/or merging the tables with eachother or maybe just to asscociate them within the datamodel. This extracting could be also done with subfield() - if there is no third parameter used subfield() will work like a loop and split every part into a new record. Here some examples - also to while-loop which will do quite the same:

Re: Splitting values within a field into individual rows on the table

Re: Loading an Array field in qlikview

- Marcus

jessiemabalhin
New Contributor II

Re: Parsing JSON data with loop

Worked for my intended purpose! Thanks a lot!