topic Re: Find All Email Address present in each line. in QlikView

Find All Email Address present in each line.

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I want to find all the email address present in each and every single line.

This is what i need:

Is there any way around for such requirement.

I have tried Index(), Textbetween() and Findoneof() functions but no Luck.

I have attached the Sample Data Source.

Regards

Eric

Re: Find All Email Address present in each line.

alexandros17 — Wed, 04 May 2016 06:58:48 GMT

The idea is :

1) look for the position of @ sympol (use index() function)

2) find the last space before and the first after @ (you can use substringcount to count number of spaces before @ symbol then with the function Findoneof you can find the position of the last space)

3) With mid function extract the string

Let me know ...

Re: Find All Email Address present in each line.

zhadrakas — Wed, 04 May 2016 07:00:48 GMT

Hello Eric,

you can use this to identify the rows with an Email Address.

wildmatch( [Column_Name], '*@*.*') as FLAG_EMAIL

but ist very hard to get the E-Mail out of this string with internal Qlik functions. (even when there are multiple Emails in one row)

i would recommend to look for another tool/way to achieve this.

Re: Find All Email Address present in each line.

Gysbert_Wassenaar — Wed, 04 May 2016 07:20:00 GMT

I'm pretty sure gabrielking123@5@gmail.com is not a valid email address. Perhaps "gabrielking123@5"@gmail.com would be. For this kind of this you really want regular expressions: How to Find or Validate an Email Address

But you can try this:

LOAD
    Column_Name,
      Part,
      Subfield(Trim(Part),' ',1) as Email
WHERE
      SubStringCount(Part,'@') and SubStringCount(Part,'.') ;
LOAD
    Column_Name,
    [Desired Result],
    SubField(subfield(Replace(Column_Name,'<-',''),':',-1),'>') as Part
FROM
      [Email Address from String - Data Source.xlsx]
      (ooxml, embedded labels, table is Sheet1)
      ;

Re: Find All Email Address present in each line.

Wed, 04 May 2016 08:16:35 GMT

Thanks Sir

The Code is Running but there are problems in some cases.

I have attached my QVW.

Re: Find All Email Address present in each line.

Gysbert_Wassenaar — Wed, 04 May 2016 08:30:22 GMT

You could remove the troublesome string first: Replace(@1,'>>??@@@SADSSDFSDV', '') as Column_Name

Re: Find All Email Address present in each line.

Anonymous — Wed, 04 May 2016 08:46:45 GMT

by using the replace function it is implemented

Re: Find All Email Address present in each line.

Wed, 04 May 2016 09:01:09 GMT

not working.

is there any way using index() or textbetween()

Re: Find All Email Address present in each line.

Gysbert_Wassenaar — Wed, 04 May 2016 09:03:16 GMT

Not working? Show me. It works fine here.

Re: Find All Email Address present in each line.

Wed, 04 May 2016 09:21:45 GMT

Is there any way which doesn't includes Hard Coding.

Re: Find All Email Address present in each line.

Gysbert_Wassenaar — Wed, 04 May 2016 09:51:01 GMT

Yes, see attached example.

Re: Find All Email Address present in each line.

Anil_Babu_Samineni — Wed, 04 May 2016 10:46:23 GMT

Hello,

Try This:

Open the Excel sheet. PFA. You can get easily after that. Try your own self. Now, take fresh QVW and then load the excel and reload. Then take fields. Check the output image. You will get same output.

Cheers,

Anil.