topic Re: Return substring after every occurrence word in Water Cooler

Return substring after every occurrence word

samp1 — Thu, 18 Mar 2021 18:35:19 GMT

I have a text field I am trying to return the value as in output which is in double codes after a specific value("text":) as in the below example, thanks for your help.

example:

TextField	Output
[{"id":"38hH6C","text":"NYC","extra_text":null},{"id":"K2kkam","text":"Phili","extra_text":null}]	NYC;Phili
[{"text":"NYC","id":"38hH6C","extra_text":null}]	NYC
[{"text":"NYC","id":"38hH6C","extra_text":null},{"text":"Washington DC","id":"T84lzU","extra_text":null}]	NYC; Washington DC

Re: Return substring after every occurrence word

QFabian — Thu, 18 Mar 2021 19:01:09 GMT

Hi @samp1 , please vheck if this works for you.

I just assumed that yor data sample where 3 rows.

///i prefered mto do it with precedent loads, or it cloud be a very large expression

Data:
LOAD @1 as TextField
FROM
[C:\Users\Fabian\Desktop\data.txt]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);

Result:
Load
TextField,
mid(B, 1, FindOneOf(B, '","')-1) as C
;
Load
TextField,
mid(A, FindOneOf(A, 'text') + 7) as B
;
Load
TextField,
subfield(TextField, '},{') as A
Resident Data;
drop table Data;

Re: Return substring after every occurrence word

Sue_Macaluso — Mon, 22 Mar 2021 13:38:39 GMT

@samp1 Are you using QlikView or Qlik Sense. I know this was answered but I would like to move it to the right product forum. Thanks!

Re: Return substring after every occurrence word

samp1 — Fri, 26 Mar 2021 12:57:46 GMT

@Sue_MacalusoI am using Qlik Sense, I fixed myself by using multiple replace functions wherever I see the extra characters.