Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
jonathanalm
New Contributor III

Transform JSON data from "CLOB"

Hi,

I have a column in a database that for performance reason stores the data in the following format:

{

  "period" : "201509",

  "child_civicno" : "202422330",

  "child_firstname" : "John",

  "child_lastname" : "Doe",

  "eventunit_code" : "NOFTH",

  "eventunit_name" : "Norreka",

  "dep_name" : "F3",

  "event_startdate" : "150608",

  "event_enddate" : "",

  "event_ratecategory" : "HEL",

  "measurementdate" : "15-SEP-15",

  "addresstype" : "PUBLIC_RECORD",

  "receiver_id" : "1287",

  "municipal_code" : "1287",

  "municipal_name" : "Galaxy"

}

So every row in column DATA has a lot of data that I need to extract and put into seperate columns.

Does anyone have a solution to transform this into columns with the corresponding data.

Kind regards, Jonathan

1 Reply
qlikdeez
Valued Contributor

Re: Transform JSON data from "CLOB"

Heres a start and use generic load as in the link provided:

The Generic Load

data:
Load 1 as Dummy,
subfield(DATA, '"' ,1) as MyColumn,
subfield(DATA, '"' ,3) as MyData
;
Load * INLINE [
DATA,
"period" : "201509",
"child_civicno" : "202422330",
"child_firstname" : "John",
"child_lastname" : "Doe",
"eventunit_code" : "NOFTH",
"eventunit_name" : "Norreka",
"dep_name" : "F3",
"event_startdate" : "150608",
"event_enddate" : "",
"event_ratecategory" : "HEL",
"measurementdate" : "15-SEP-15",
"addresstype" : "PUBLIC_RECORD",
"receiver_id" : "1287",
"municipal_code" : "1287",
"municipal_name" : "Galaxy"
]
;