JSON Parse

[jemi]

How to parse a JSON column from SQL to QlikSense? The SQL table I am loading in QlikSense has a JSON column called calculatedvalues. I have attached 3 rows of that column.

calculatedvalues:

{"reversetax":0,"discountrate":0,"taxexclusiverp":7500,"taxexclusive_discountedsaleprice":7500,"saletaxes_rate":[],"roundedbilledvalue":7500,"roundedconcession":0,"roundedamount":7500,"accountsubtotal":7500,"accountgrandtotal":7500,"accountprecisegrandtotal":7500,"accountroundoff":0,"accounttotalconcession":0,"accounttotaltax":0}

{"reversetax":0,"discountrate":0,"taxexclusiverp":750,"taxexclusive_discountedsaleprice":750,"saletaxes_rate":[],"roundedbilledvalue":750,"roundedconcession":0,"roundedamount":750,"accountsubtotal":750,"accountgrandtotal":750,"accountprecisegrandtotal":750,"accountroundoff":0,"accounttotalconcession":0,"accounttotaltax":0}

{"reversetax":20.097457627118647,"discountrate":0.1,"taxexclusiverp":111.65254237288136,"taxexclusive_discountedsaleprice":100.48728813559322,"saletaxes_rate":[{"taxId":"E5F07E78-1081-4DAC-B57E-E344ADCC4541","title":"CGST","percent":9,"rate":0.09,"amount":9.043855932203389,"totaltaxamount":9.043855932203389,"roundedtotaltaxamount":9.04},{"taxId":"B0E910B5-A841-4460-8CC5-F068A13337C4","title":"SGST","percent":9,"rate":0.09,"amount":9.043855932203389,"totaltaxamount":9.043855932203389,"roundedtotaltaxamount":9.04}],"roundedbilledvalue":131.75,"roundedconcession":13.18,"roundedamount":118.57,"accountsubtotal":691.75,"accountgrandtotal":623,"accountprecisegrandtotal":622.58,"accountroundoff":0.42,"accounttotalconcession":69.18,"accounttotaltax":79.22,"accountcompanycredit":0,"accountpatientcash":622.58,"accountroundedpatientcash":623,"accountpatientcredit":0}

How do I access certain keys like "accounttotaltax", "accountgrandtotal" from QlikSense? Every row has different number of keys, so string functions wont work. What else can I do?

I am new to QlikSense. Any help would be highly appreciated.

[SerhanKaraer · Accepted solution]

Hello jemi,

There is an undocument json parse property in from_field function as you see below:

Data:
LOAD '{"reversetax":0,"discountrate":0,"taxexclusiverp":7500,"taxexclusive_discountedsaleprice":7500,"saletaxes_rate":[],"roundedbilledvalue":7500,"roundedconcession":0,"roundedamount":7500,"accountsubtotal":7500,"accountgrandtotal":7500,"accountprecisegrandtotal":7500,"accountroundoff":0,"accounttotalconcession":0,"accounttotaltax":0}' as jsonText AutoGenerate 1;
LOAD '{"reversetax":0,"discountrate":0,"taxexclusiverp":750,"taxexclusive_discountedsaleprice":750,"saletaxes_rate":[],"roundedbilledvalue":750,"roundedconcession":0,"roundedamount":750,"accountsubtotal":750,"accountgrandtotal":750,"accountprecisegrandtotal":750,"accountroundoff":0,"accounttotalconcession":0,"accounttotaltax":0}' as jsonText AutoGenerate 1;
LOAD '{"reversetax":20.097457627118647,"discountrate":0.1,"taxexclusiverp":111.65254237288136,"taxexclusive_discountedsaleprice":100.48728813559322,"saletaxes_rate":[{"taxId":"E5F07E78-1081-4DAC-B57E-E344ADCC4541","title":"CGST","percent":9,"rate":0.09,"amount":9.043855932203389,"totaltaxamount":9.043855932203389,"roundedtotaltaxamount":9.04},{"taxId":"B0E910B5-A841-4460-8CC5-F068A13337C4","title":"SGST","percent":9,"rate":0.09,"amount":9.043855932203389,"totaltaxamount":9.043855932203389,"roundedtotaltaxamount":9.04}],"roundedbilledvalue":131.75,"roundedconcession":13.18,"roundedamount":118.57,"accountsubtotal":691.75,"accountgrandtotal":623,"accountprecisegrandtotal":622.58,"accountroundoff":0.42,"accounttotalconcession":69.18,"accounttotaltax":79.22,"accountcompanycredit":0,"accountpatientcash":622.58,"accountroundedpatientcash":623,"accountpatientcredit":0}' as jsonText AutoGenerate 1;

JsonTable:
LOAD accounttotaltax, accountgrandtotal From_Field(Data, jsonText) (json, utf8, no labels);

 I hope it solves your issue.

[jemi · Accepted solution]

Thank you. I did something similar and it worked.

Load
calculatedvalues,
saleitemId;

[WorkItems]:
select  calculatedvalues, saleitemId from saleitem;  //saleitem -> tablename in which calculatedvalues is a                                                                                                                                                       column

LOAD * From_Field(WorkItems, calculatedvalues) (json, utf8, no labels);

[SerhanKaraer]

Could you please mark solution as correct? 

[CasperQlik]

I get the following error when I use the From_Field function:

This is my code:

Any help is appreciated!

[harshal852001]

Hi SerhanKaraer,

Do you have any solution data load "Qliksense "read  the SQL table having  couple of fields having various JSON format values ,what is appropriate way to LOAD in model.

Kindly suggest if you aware

Thanks

[harshal852001]

if you find any solution let me know ..i'm looking for same solution you are working..

[CasperQlik]

I found a solution. For me I just had to remove any rows that contained no JSON. 

https://community.qlik.com/t5/Design/FROM-FIELD-Is-very-powerful-It-helped-me-parse-JSON-data-stored...