topic Re: In Between text in QlikView

In Between text

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

I need to create a column XXX where i should get the Number between the text.

I applied the logic TExtBEtween(name,'(',')') and i worked for just one scenario

Can anyone help me out.

id state name XXX

abc BC alb - ik me (abc def)(1234) 1234

abc BC alb - ik me (1234) 1234

Re: In Between text

neelamsaroha157 — Fri, 07 Sep 2018 17:51:44 GMT

Try - KeepChar(name, '0123456789')

Re: In Between text

vardhan1305 — Fri, 07 Sep 2018 17:58:01 GMT

Hi Neelam. I got the output but sorry i forgot to write another scenario

id state name XXX

abc BC alb - ik me (abc def)(1234) 1234

abc BC alb - ik me (1234) 1234

abc BC alb - ik me (pittsburgh) pittsburgh

I have 3 scenarios and can you suggest what to do in this scenario??

Re: In Between text

neelamsaroha157 — Fri, 07 Sep 2018 18:00:30 GMT

So do you want the text/number between the last parenthesis??

Re: In Between text

vardhan1305 — Fri, 07 Sep 2018 18:05:47 GMT

Yeah. alb - ik me " is the name and if there is any number in the parenthesis (1234) it should display the number or else " pittsburgh ".

KeepChar(name, '0123456789') worked for 2 scenarios and i need to apply for the other one too.

name XXX

alb - ik me (abc def)(1234) 1234

alb - ik me (1234) 1234

alb - ik me (pittsburgh) pittsburgh

Re: In Between text

santhana — Fri, 07 Sep 2018 18:41:36 GMT

Hi Harsha Vardhan Kindly use the below code it will meet your desired output

data:

load * Inline [

id , state , name

abc , BC , alb - ik me (abc def)(1234)

abc , BC , alb - ik me (abc def)(12345)

abc , BC , alb - ik me (12346)

abc , BC , alb - ik me (1234)

abc , BC , alb - ik me (pittsburgh) ];

load

id,

state,

name ,

purgechar(subfield(name,'(',-1),')') as XXXX,

'*' as temp Resident data ;

drop table data;

Thanks and Regards

Santhana Kumar

Re: In Between text

santhana — Fri, 07 Sep 2018 18:44:29 GMT

Hi use this

purgechar(subfield(name,'(',-1),')') as XXXX,