Parsing JSON data with loop

[jessiemabalhin]

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"]]}
65934	133422	{"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!

[marcus_sommer · Accepted solution]

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]

Worked for my intended purpose! Thanks a lot!