Help parsing a varchar from db

[Anonymous]

A table in one of my databases has a varchar field in which there is multiple data that require parsing.
The format in which the data is in that varchar field is in the format: {"<visit_type_id>":"[<expected_visits_overall>,<expected_visits_cycle_1>,<expected_visits_cycle_2>,<expected_visits_cycle_3>]"}
There can be one or more visit_type_ids with the corresponding expected_visits, separated by commas.
Example for a single visit_type_id: {"10":"[0,0,0,0]"}

Example for multiple visit_type_ids: {"10":"[0,0,0,0]","7":"[1,2,3,4]","6":"[5,6,7,8]","5":"[9,10,11,12]"}
How can I parse this varchar into either an array or a new table?
An example of table I would like to obtain is(for the multiple visit_type_ids example I provided above):

Visit_Type_ID	Expected_Visits_Overall	Expected_Visits_Cycle_1	Expected_Visits_Cycle_2	Expected_Visits_Cycle_3
10	0	0	0	0
7	1	2	3	4
6	5	6	7	8
5	9	10	11	12

[Anil_Babu_Samineni]

How we are getting data? XML Format / JSON Format?

[Anonymous]

The data is stored in a table from the database that I am connected to.

[Anil_Babu_Samineni]

This is difficult to read as per my analysis. For a work around, You could try. Make sure - This should parse 5 parameters only with in array..

SET Measure = Replace(

     Replace(

          Replace(

               SubField(

                    Trim(

                         Replace(

                              Replace(

                                   Replace(

                                        Replace(

                                             '{"10":"[0,0,0,0]","7":"[1,2,3,4]","6":"[5,6,7,8]","5":"[9,10,11,12]"}',

                                        '{',' '),

                                   '}',' '),

                              '"',' '),

                         ':','@')

                    ),

               ']'),

          '[', ' '),

     '@', ' '),

',', ' ');

For i=1 to 5

LOAD SubField($(Measure), $(i), ' ') as expected_visits_overal$(i)

AutoGenerate $(i);

Next i

[rwunderlich · Accepted solution]

How about:

LOAD

SubField(Segment, ',', 1) as visit_type_id,

SubField(Segment, ',', 2) as expected_visits_overall,

SubField(Segment, ',', 3) as expected_visits_cycle_1,

SubField(Segment, ',', 4) as expected_visits_cycle_2,

SubField(Segment, ',', 5) as expected_visits_cycle_3

;

LOAD

Replace(Purgechar(SubField(dbfield, '","'),'"{}[]'),':',',') as Segment

;

SQL SELECT dbfield FROM ...;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[Anonymous]

I'm trying to understand how this works. SubField creates a new in-memory table ?

[rwunderlich]

SubField() extract substrings from a string:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/StringFunction...

-Rob