topic How to read JSON data in Qlikview in QlikView

How to read JSON data in Qlikview

bhanuchander558 — Wed, 25 Nov 2020 16:16:04 GMT

HI All,

I have one field in a table which has data in JSON format like below:

{"name":"John", "age":31, "city":"New York"}

I want Name, age, city as separate fields under which it should come John,31 and New York respectively.

Anyone previously came across this or your help on this could be great.

Re: How to read JSON data in Qlikview

tresB — Tue, 30 Oct 2018 06:51:25 GMT

If the data is consistent like you showed, you could probably use string parsing functions like subfield(). See this:Re: Parsing JSON data with loop

For more complex structure, look at:

https://community.qlik.com/docs/DOC-16915

Load Data by Script with JSON

Re: How to read JSON data in Qlikview

rwunderlich — Tue, 30 Oct 2018 11:57:14 GMT

One approach I like because it works for any number of key:value pairs:

Final:

Generic Load RecId, Key, Value

;

LOAD

RecId,

trim(SubField(Pair,':',1)) as Key,

trim(SubField(Pair,':',2)) as Value

;

LOAD

RecId,

SubField(Raw,',') as Pair

;

LOAD

RecNo() as RecId,

purgeChar(Raw,'{}"') as Raw

Inline [

Raw

{"name":"John", "age":31, "city":"New York"}

] (delimiter is '|')

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

Re: How to read JSON data in Qlikview

bhanuchander558 — Wed, 31 Oct 2018 09:38:03 GMT

Thanks that works for me.

Re: How to read JSON data in Qlikview

bhanuchander558 — Wed, 31 Oct 2018 09:40:46 GMT

Thanks for your comment, I have parsing of string using for loop for that array string but my concern is if in future the format changes with any delimiter then its an issue.

Re: How to read JSON data in Qlikview

Prabhu — Tue, 05 Mar 2019 11:38:12 GMT

Hi Rob,
I followed the above procedure. But, the association between the columns is not working when we have multiple values in the columns. Like this
{"name":"John", "age":31, "city":"New York","name":"Deb", "age":33, "city":"Hyderabad"}