Water Cooler

[samp1]

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

[QFabian]

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;

[Sue_Macaluso]

@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!

[samp1 · Accepted solution]

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

[johnson111]

thanks for sharing. i got solution.