topic Filtering out invalid email addresses in QlikView

Filtering out invalid email addresses

Thu, 26 Apr 2012 07:58:38 GMT

Morning, I cant get the script below to produce the result I want, on my system at work you can't have a blank email address field for customer so we put N@ or n@. So I wrote a script below to exclude these for an audit.

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

or (left(Email_Address,2)<>'N@')

or (left(Email_Address,2)<>'n@')

;

This is still giving me the the emails I dont want.

i'm a bit baffled as this looks like the correct way to do it.

Paul.

Filtering out invalid email addresses

Miguel_Angel_Baeyens — Thu, 26 Apr 2012 08:04:24 GMT

Hi,

Do you mean you don't want to load all email addresses from the QVD file that start with "N@"? If so, the following should work:

WHERE Upper(Left(Email_Address, 2)) <> 'N@';

Hope that helps.

Miguel

Filtering out invalid email addresses

CELAMBARASAN — Thu, 26 Apr 2012 09:27:44 GMT

Hi,

In your way it should be

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

or (left(Email_Address,2)<>'N@'

and left(Email_Address,2)<>'n@')

;

Its better to use upper or Lower functions to make case insensitive comparison

where Standard_Sender_Address='X'

or Upper(left(Email_Address,2))<>'N@'

;

where Standard_Sender_Address='X'

or Lower(left(Email_Address,2))<>'N@'

;

or use wildmatch

where Standard_Sender_Address='X'

or not WildMatch(Email_Address,'N@*')

;

or use MixMatch

where Standard_Sender_Address='X'

or MixMatch(left(Email_Address,2)),'N@')

;

I suggest you to use wildMatch.

Celambarasan

Filtering out invalid email addresses

Mon, 30 Apr 2012 12:10:09 GMT

I fiinally figured it out! The script below will help filter out unwanted email addresses where people have filled in a free format mix of n/a,n@a or ask (change it relevent to the data on your specific system)

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

and not WildMatch(Email_Address,'N@*')

and not WildMatch(Email_Address,'N'&chr(39)&'@*') This filters out email addresses with n’@* in it

and not WildMatch(Email_Address,'ask*')

and not WildMatch(Email_Address,'n/a*')

and not WildMatch(Email_Address,'na@*')

and lower(right(Email_Address,3))<>'n/a'