topic Re: SubField issue on Qlikview in QlikView

SubField issue on Qlikview

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

Hi Everyone,

I have a situation where I need to pull only 4 characters [ZZZZ] before @ symbol in the email formats shown below

Tom.x.harry.peter.ZZZZ@gmail.com

Tom.x.harry.ZZZZ@gmail.com

Tom.x.ZZZZ@gmail.com

Tom.x.harry.peter.o.ZZZZ@gmail.com

I tried below but it works for only one situation

subField(SubField(upper(Column1),'.',3),'@'.1)

Looking forward for the lead

Thanks very much.

Re: SubField issue on Qlikview

neelamsaroha157 — Tue, 30 May 2017 20:54:48 GMT

How about this:

=Mid(Mail, Index(Mail, '@')-4,4)

Re: SubField issue on Qlikview

remo0017 — Wed, 31 May 2017 02:33:03 GMT

Nopes that doesn't work I tried it

Thanks very much for the above suggestion Neelam

Re: SubField issue on Qlikview

Anil_Babu_Samineni — Wed, 31 May 2017 02:37:47 GMT

How about this?

=SubField(SubField(FieldName,'@', 1),'.',-1)

Re: SubField issue on Qlikview

sunny_talwar — Wed, 31 May 2017 02:45:42 GMT

Both suggestions seems to be working

Table:

LOAD *,

SubField(SubField(Email, '@', 1), '.', -1) as NewField,

Mid(Email, Index(Email, '@')-4,4) as NewField2;

LOAD * INLINE [

Email

Tom.x.harry.peter.ZZZZ@gmail.com

Tom.x.harry.ZZZZ@gmail.com

Tom.x.ZZZZ@gmail.com

Tom.x.harry.peter.o.ZZZZ@gmail.com

];

The one in Red is proposed by Anil and the one in Blue is by Neelam.

Re: SubField issue on Qlikview

remo0017 — Wed, 31 May 2017 11:39:30 GMT

These work Guys, thanks very much for your help

Much appreciated Anil, Sunny and Neelam

Re: SubField issue on Qlikview

sunny_talwar — Wed, 31 May 2017 11:42:40 GMT

Please mark Neelam/Anil's response as correct because I simply took there expressions and create a script for you. or at least mark there responses as helpful as they put in the effort to come up with the correct response and as I just showed, both the responses seems to be working.

Thank you,

Sunny