topic Re: Find search string within text in script in App Development

Find search string within text in script

Sagar_Apte — Sat, 16 Nov 2024 01:55:40 GMT

Hi All,

I need to build the logic in script for below data scenario

ID text_description search_string
1 abc;N:xyz;abc;N:pqr;K:123;N:123; N
2 B:abc;xyz;abc;B:123;xyz;N:123; B
3 123;C:test;C:234;abc;pqr;B:xyz; C

text_description always starts with some texts and end with ';' and in between texts are also separated by ';'.

I want to search value of search_string column in text_description column and extract the part of string with below logic

Let's take a example of ID =1
text_description is abc;N:xyz;abc;N:pqr;K:123;N:123;
search_string is 'N'

Now I need to search 'N' followed by ':' in text_description and extract the text between 'N:'& ';'

'N:' could be anywhere in the string and could be repeated multiple times. Hence for each occurrence we need to extract the texts

Output for ID=1 will be
ID text_description String
1 abc;N:xyz;abc;N:pqr;K:123;N:123; xyz;pqr

Similar logic should work for other search string.

This is fake data as sample data will be difficult to share but in actual scenario format of the data will be same.

Any help will be really appreciated.

Re: Find search string within text in script

Taoufiq_Zarra — Mon, 13 Jul 2020 11:07:39 GMT

Hi,

One Solution :

Data: load subfield(if(Match(left(TmpTxt,2),search_string&':')>0,TmpTxt),':',2) as Txt,* ; load subfield(text_description,';') as TmpTxt,* inline [ ID,text_description,search_string 1,abc;N:xyz;abc;N:pqr;K:123;N:123;,N 2,B:abc;xyz;abc;B:123;xyz;N:123;,B 3,123;C:test;C:234;abc;pqr;B:xyz;,C ]; output: noconcatenate load Concat(distinct text_description) as text_description,ID,concat(Txt,';') as String,Concat(distinct search_string) as search_string resident Data where len(trim(Txt))>0 group by ID; drop table Data;

output :

Re: Find search string within text in script

Sagar_Apte — Tue, 14 Jul 2020 09:03:44 GMT

@Taoufiq_Zarra Thanks for your reply. I already got the solution here. I don't know how two posts are created.

https://community.qlik.com/t5/Qlik-Sense-App-Development/Find-search-string-within-text-in-script/m-p/1727120#M55419