Qliksense json code parser

[krmvacar]

Hi all, 

I have a table and one of the fields in this table comes as json. Here I want to make this calculation in the field that comes as json. I want to show the total of the product counts between two barcodes in the new field. I want the calculation to end when I see the second barcodes field.

Tables:

1-

[{"SS":"SS096084","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"Tek","Miktar":8300,"barcodes":[{"MainItemId":"IZO1000003","LotNr":"IV44688909","Type":"","width":1200,"prodCount":1,"Miktar":8300,"Dara":1.92,"special1":"1200 MM YANMAZ ALU FOLYO","special2":"","special3":"Eylül 2024","special4":"","special5":"","special6":"00190144","special8":1000,"special9":1200}]}]

2- [{"SS":"SS101505","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"CokluKombine","Miktar":4201,"barcodes":[{"MainItemId":"DGY1000087-03","LotNr":"IV47822711","Type":"balkan","width":368,"prodCount":2,"Miktar":2800,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(BALKAN)(2 BANT)","special2":"21658-B","special3":"18753","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368},{"MainItemId":"DGY1000071-03","LotNr":"IV47822705","Type":"dünya","width":368,"prodCount":1,"Miktar":1400,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(DÜNYA)(TEK BANT)","special2":"21658-B","special3":"18753","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368}]},{"SS":"SS101506","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"CokluKombine","Miktar":4201,"barcodes":[{"MainItemId":"DGY1000087-03","LotNr":"IV47822717","Type":"balkan","width":368,"prodCount":2,"Miktar":2800,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(BALKAN)(2 BANT)","special2":"21658-B","special3":"18754","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368},{"MainItemId":"DGY1000071-03","LotNr":"IV47822713","Type":"dünya","width":368,"prodCount":1,"Miktar":1400,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(DÜNYA)(TEK BANT)","special2":"21658-B","special3":"18754","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368}]},{"SS":"SS102096","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"CokluKombine","Miktar":6300,"barcodes":[{"MainItemId":"DGY1000087-03","LotNr":"IV48204473","Type":"balkan","width":368,"prodCount":2,"Miktar":4200,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(BALKAN)(2 BANT)","special2":"21658-D","special3":"18753","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368},{"MainItemId":"DGY1000071-03","LotNr":"IV48204474","Type":"dünya","width":368,"prodCount":1,"Miktar":2100,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(DÜNYA)(TEK BANT)","special2":"21658-D","special3":"18753","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368}]},{"SS":"SS102097","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"CokluKombine","Miktar":6300,"barcodes":[{"MainItemId":"DGY1000087-03","LotNr":"IV48204484","Type":"balkan","width":368,"prodCount":2,"Miktar":4200,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(BALKAN)(2 BANT)","special2":"21658-D","special3":"18754","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368},{"MainItemId":"DGY1000071-03","LotNr":"IV48204536","Type":"dünya","width":368,"prodCount":1,"Miktar":2100,"Dara":0.77,"special1":"OPP PATOS ROLLS SP.PLUS(DÜNYA)(TEK BANT)","special2":"21658-D","special3":"18754","special4":"","special5":"","special6":"00197477","special8":1000,"special9":368}]}]

3-[{"SS":"SS101511","BarkodeID":"c264fb35-1775-4b9e-8faf-1a2d267ef64e","DilmeTip":"Tek","Miktar":2110,"barcodes":[{"MainItemId":"DGY1000235-01","LotNr":"IV47822870","Type":"","width":410,"prodCount":2,"Miktar":2110,"Dara":0.86,"special1":"OPP AMİGO TACO BAH. SÜPER(kg)(FLEKSO)","special2":"21634","special3":"18753","special4":"","special5":"291.45 mm","special6":"00197520","special8":1000,"special9":410}]}]

I want to Result :

 ProdCount

1

3

2

Best Regards

[nhenckel · Accepted solution]

To work with JSON data like this in Qlik Sense, one approach is to leverage the JSON functions (JsonGet, IsJson, etc.) to extract the data into a structured format. Once the data is in a standard Qlik table, you can perform your calculations and aggregations more easily.

Here’s an example script that extracts the prodCount values from the JSON field into a Qlik data table:

SET json =
[
  {
    "SS": "SS096084",
    "BarkodeID": "c264fb35-1775-4b9e-8faf-1a2d267ef64e",
    "DilmeTip": "Tek",
    "Miktar": 8300,
    "barcodes": [
      {
        "MainItemId": "IZO1000003",
        "LotNr": "IV44688909",
        "Type": "",
        "width": 1200,
        "prodCount": 1,
        "Miktar": 8300,
        "Dara": 1.92,
        "special1": "1200 MM YANMAZ ALU FOLYO",
        "special2": "",
        "special3": "Eylül 2024",
        "special4": "",
        "special5": "",
        "special6": "00190144",
        "special8": 1000,
        "special9": 1200
      }
    ]
  },
  ... continued
];

jsonExtract:
Load
	jsonBarcodes,
    JsonGet(jsonBarcodes, '/prodCount') AS prodCount;
LOAD
	jsonItems,
    JsonGet(jsonItems, '/barcodes/'&(IterNo()-1)) AS jsonBarcodes
WHILE IsJson(JsonGet(jsonItems, '/barcodes/' & (IterNo()-1)));
LOAD
    JsonGet(json, '/'&(IterNo()-1)) AS jsonItems
WHILE IsJson(JsonGet(json, '/' & (IterNo()-1)));

LOAD 
	'$(json)' as json 
AutoGenerate 1;

Explanation:

1. Iteratively Parse the JSON:

   • The script uses JsonGet() and IterNo() to loop through each object in the JSON array and extract the barcodes array for each entry.
   • It then loops through each barcodes array to extract prodCount.

2. Output:

   • The jsonExtract table will contain the prodCount values extracted from the JSON.

3. Perform Aggregations:

   • Once the data is in table format, you can aggregate prodCount values or perform any other operations using Qlik expressions.