topic Re: TextBetween some symbols in QlikView

TextBetween some symbols

Wed, 06 Aug 2014 04:04:26 GMT

Hi Friends,

Please help me.. This is urgent for me...

I have a feild having values like below:-

Name

shamly.gmail.com

abc_shamly.gmail.com

kevin.gmail.com

abc_george.gmail.com

abc_def_shamly.gmail.com

abc_shamly

shamly

kevin

george

I need only the nams from all the feilds. Some name entries are there as default(kevin, shamly, george). I need those as such and shamy from other entries which has '_ 'and '.' . So I used TextBetween function. But it is not giving me for all. Please help me to get only NAME from all the given possibilities of name entries.

I tried this trim(UPPER(subfield(Name,'.',1)))

I tried this also UPPER(TextBetween(NAME, '_', '.',1))

Please help me quickly friends... this is vey important for me today

Message was edited by: Shamly P.T

Re: TextBetween some symbols

Anonymous — Wed, 06 Aug 2014 04:13:32 GMT

Hi,

Use WildMatch function to get this done. Something like below,

If(WildMatch(Name, '*shamly*'), 'Shamly')

Re: TextBetween some symbols

tresB — Wed, 06 Aug 2014 04:17:08 GMT

Load

If(WildMatch(Name, '*shamly*'), 'SHAMLY') as Name

Re: TextBetween some symbols

Wed, 06 Aug 2014 04:26:47 GMT

Hi Satyadev,

Sorry to notify late. Shamly is not a unique entry. It can have any number of different names like below

shamly.gmail.com

abc_kevin.gmail.com

abc_shamly.gmail.com

abc_def_george.gmail.com

abc_shamly

shamly

kevin

George

How to get only the names…?????

Regards,

Shamly P T

Program Transformation Office

• Mobile: +91 - 8088266881

• Email: shamly.t95@wipro.com<mailto:shamly.t95@wipro.com>

“Whatever the mind can conceive and believe,it can achieve”

Re: TextBetween some symbols

Wed, 06 Aug 2014 04:27:23 GMT

Hi Satyadev,

Sorry to notify late. Shamly is not a unique entry. It can have any number of different names like below

shamly.gmail.com

abc_kevin.gmail.com

abc_shamly.gmail.com

abc_def_george.gmail.com

abc_shamly

shamly

kevin

George

How to get only the names…?????

Regards,

Shamly P T

Program Transformation Office

• Mobile: +91 - 8088266881

• Email: shamly.t95@wipro.com<mailto:shamly.t95@wipro.com>

“Whatever the mind can conceive and believe,it can achieve”

Re: Re: TextBetween some symbols

aveeeeeee7en — Wed, 06 Aug 2014 04:51:22 GMT

Hi Shamly

Use this Script:

AAA:
Load *, Subfield(Name,'.',1) AS NewName Inline [
Name
shamly.gmail.com
abc_kevin.gmail.com
abc_shamly.gmail.com
abc_def_george.gmail.com
abc_shamly
shamly
kevin
George
];

BBB:
Load *,
if(len(Subfield(NewName,'_',3))>3,Subfield(NewName,'_',3),
if(len(Subfield(NewName,'_',2))>3,Subfield(NewName,'_',2),
if(len(Subfield(NewName,'_',1))>3,Subfield(NewName,'_',1),NewName))) AS LatestName
Resident AAA;
DROP Table AAA;

Hop that helps.

See the Attachment.

Re: TextBetween some symbols

tresB — Wed, 06 Aug 2014 05:16:39 GMT

This works:

Load Name,

TextBetween('_'&Name&'.', '_','.',If(SubStringCount(Name,'_')=0,1,SubStringCount(Name,'_')+1)) as NewNames;

Load * Inline [

Name

shamly.gmail.com

abc_kevin.gmail.com

abc_shamly.gmail.com

abc_def_george.gmail.com

abc_shamly

shamly

kevin

George]

Re: TextBetween some symbols

Anonymous — Wed, 06 Aug 2014 06:06:09 GMT

Hi Shamly,

Try below,

SubField(SubField(Name, '.', 1), '_', SubStringCount(Name, '_')+1) as NewName

Re: TextBetween some symbols

SunilChauhan — Wed, 06 Aug 2014 06:18:12 GMT

could you please specify the pattern you are following

ex: something_emailid_something_somename

Re: TextBetween some symbols

MarcoWedel — Fri, 08 Aug 2014 23:29:39 GMT

another one:

tabNames:
LOAD Name, SubField(NameNew, '_', SubStringCount(NameNew, '_')+1) as NameNew;
LOAD Name, SubField(Name, '.', 1) as NameNew
Inline [
Name
shamly.gmail.com
abc_shamly.gmail.com
kevin.gmail.com
abc_george.gmail.com
abc_def_shamly.gmail.com
abc_shamly
shamly
kevin
george
];

hope this helps

regards

Marco

Re: TextBetween some symbols

evan_kurowski — Sat, 09 Aug 2014 00:52:36 GMT