2 Replies Latest reply: Jun 6, 2018 9:26 PM by Jessie Carl Mabalhin RSS

    Parsing JSON data with loop

    Jessie Carl Mabalhin

      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!