Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

Load long string from TextFile and split into Rows

HI,

I have a json string within a text file that I would like to split into Rows.

when openined in notepad the string looks like :

{"APIResults":{"10008": {"ID": "9108471", "Value": "0", "Input": "N/A"}, {"10009": {"ID": "41066228", "Value": "0", "Input": "N/A"}, {"100010": {"ID": "174106", "Value": "0", "Input": "N/A"}, {"100011": {"ID": "601057808", "Value": "0", "Input": "N/A"}, {"100012": {"ID": "8030454", "Value": "0", "Input": "N/A"}}, "Summary": {"TotalCount": 8656, "ProcessingTimeSeconds": 156.242008}, "APIMessage": {"StatusCode": 200, "Message": "SUCCESS"}}

the row delimeter is a '},' so the resulting table should look like :

{"10008": {"ID": "9108471", "Value": "0", "Input": "N/A"},
{"10009": {"ID": "41066228", "Value": "0", "Input": "N/A"},
{"100010": {"ID": "174106", "Value": "0", "Input": "N/A"},
{"100011": {"ID": "601057808", "Value": "0", "Input": "N/A"},
{"100012": {"ID": "8030454", "Value": "0", "Input": "N/A"}

 

If possible I would like to omit the first part : {"APIResults":

and also the trailing details at the end :
"Summary": {"TotalCount": 8656,  "ProcessingTimeSeconds": 156.242008}, "APIMessage": {"StatusCode": 200, "Message": "SUCCESS"}} 

Is this possible using a load script?

Many thanks, Phil

Labels (3)
1 Solution

Accepted Solutions
Highlighted

Re: Load long string from TextFile and split into Rows

May be this

 

Table:
LOAD Replace(FieldName, '{"APIResults":', '') as FieldName
Where Not WildMatch(FieldName, '*Summary*', '*APIMessage*');
LOAD SubField(@1, '},') & '}' as FieldName
FROM
[Load long string from TextFile and split into Rows.txt]
(txt, codepage is 1252, explicit labels, delimiter is '\t', msq);

 

View solution in original post

1 Reply
Highlighted

Re: Load long string from TextFile and split into Rows

May be this

 

Table:
LOAD Replace(FieldName, '{"APIResults":', '') as FieldName
Where Not WildMatch(FieldName, '*Summary*', '*APIMessage*');
LOAD SubField(@1, '},') & '}' as FieldName
FROM
[Load long string from TextFile and split into Rows.txt]
(txt, codepage is 1252, explicit labels, delimiter is '\t', msq);

 

View solution in original post