topic Split Big String in Multiple Fields? in QlikView

Split Big String in Multiple Fields?

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

Hi,

I have the following string:

[{"id":"1","program":"prog003","number":"100","datetime":"2017-01-03","zone":"north","amount":"100"}, {"id":"2","program":"prog003","number":"200","datetime":"2017-01-04","zone":"north","amount":"300"}, {"id":"3","program":"prog004","number":"300","datetime":"2017-01-05","zone":"south","amount":"150"},{"id":"4","program":"prog004","number":"400","datetime":"2017-01-03","zone":"east","amount":"500"},{"id":"5","program":"prog005","number":"500","datetime":"2017-01-04","zone":"west","amount":"450"},{"id":"6","program":"prog006","number":"600","datetime":"2017-01-05","zone":"east","amount":"200"},{"id":"7","program":"prog007","number":"700","datetime":"2017-01-06","zone":"west","amount":"150"},{"id":"8","program":"prog007","number":"800","datetime":"2017-01-07 ","zone":"south","amount":"200"}]

I need to use String Functions by script in order to get a table with the following:

id	program	number	datetime	zone	amount
1	prog003	100	2017-01-03	north	100
2	prog003	200	2017-01-05	north	300
3	prog004	300	2017-01-05	south	150
...	...	...	...	...	...

Is there any way to split it with functions by script so I could have this?

Thanks!

Re: Split Big String in Multiple Fields?

Anil_Babu_Samineni — Mon, 20 Mar 2017 13:04:40 GMT

Where this JSON format stored. I mean Txt or what?

Re: Split Big String in Multiple Fields?

microwin88x — Mon, 20 Mar 2017 13:08:49 GMT

It's a URL. But the thing is I would need to use String Functions like SubField/TextBetween/etc because I was asked to do it that way instead of using macros or other connectors... Do you know how could I do that?

Re: Split Big String in Multiple Fields?

MarcoWedel — Tue, 21 Mar 2017 22:21:09 GMT

Hi,

besides a "real" JSON parser being the best solution for this requirement, a script-only solution for this specific source data could be:

tabTemp:
Generic
LOAD ID,
     SubField(SubString,'"',2),
     SubField(SubString,'"',4);
LOAD SubField(TextBetween(BigString,'{','}',IterNo()),',') as SubString,
     IterNo() as ID
Inline [
BigString
{"id":"1","program":"prog003","number":"100","datetime":"2017-01-03","zone":"north","amount":"100"}, {"id":"2","program":"prog003","number":"200","datetime":"2017-01-04","zone":"north","amount":"300"}, {"id":"3","program":"prog004","number":"300","datetime":"2017-01-05","zone":"south","amount":"150"},{"id":"4","program":"prog004","number":"400","datetime":"2017-01-03","zone":"east","amount":"500"},{"id":"5","program":"prog005","number":"500","datetime":"2017-01-04","zone":"west","amount":"450"},{"id":"6","program":"prog006","number":"600","datetime":"2017-01-05","zone":"east","amount":"200"},{"id":"7","program":"prog007","number":"700","datetime":"2017-01-06","zone":"west","amount":"150"},{"id":"8","program":"prog007","number":"800","datetime":"2017-01-07 ","zone":"south","amount":"200"}
] (delimiter is '\t')
While IterNo()<=SubStringCount(BigString,'{');

tabResult:
LOAD 1 AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp.*') THEN
  JOIN (tabResult) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Field 1;

hope this helps

regards

Marco

Re: Split Big String in Multiple Fields?

MarcoWedel — Thu, 23 Mar 2017 19:44:08 GMT

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco