topic Get value between two special characters. in QlikView

Get value between two special characters.

renjithpl — Fri, 04 Nov 2011 10:02:39 GMT

Hi All,

i have a table like below:

Emailid	First Name	Last Name
marguerite.e.sheehan@idea.com	Marguerite

Last Name may be blank sometimes, i need to find a formula which will say, if (Last Name is blank, then Last Name should be from email id between '.' and '@')

The problem i have here is, the email id has middle name,

my output should look like:

Emailid	First Name	Last Name
marguerite.e.sheehan@idea.com	Marguerite	Sheehan

i tried something with subfield, but its not giving the right answer.

Any Help?

Get value between two special characters.

Fri, 04 Nov 2011 10:05:42 GMT

Quickest way i can think of is :

subfield(subfield('marguerite.e.sheehan@idea.com','.',3),'@',1)

Have Fun,

Get value between two special characters.

swuehl — Fri, 04 Nov 2011 10:23:23 GMT

I think this a bit shorter

textbetween('marguerite.e.sheehan@idea.com','.','@',2)

If you want the name capitalized, use

Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',2))

and if you have records with middle name and some without, i.e. 1 or two occurences of '.' before the '@', use something like this

=Capitalize(textbetween('marguerite.e.sheehan@idea.com','.','@',

SubStringcount(left('marguerite.e.sheehan@idea.com',index('marguerite.e.sheehan@idea.com','@')),'.')))

Of course you should replace 'marguerite.e.sheehan@idea.com' with your field name.

Regards,

Stefan