topic Re: Valid Email Addresses in QlikView

Valid Email Addresses

mcoetzee — Wed, 24 Apr 2019 12:04:52 GMT

Hi,

I am trying to check for valid and invalid email addresses in my data, please see formula done below.

I am however still getting data data should not be in the valid field.

If(Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000' Or Right (IDNumber, '/') Or Right (IDNumber, 'NPO' Or Right(IDNumber, '()')), 'Invalid', 'Valid') as [IDnumber Valid],

Sample Data:

0000/00/00000

NPO/000/0000

0000/00/000(00)

Re: Valid Email Addresses

mcoetzee — Wed, 24 Apr 2019 12:44:44 GMT

Sorry not email address, ID numbers.

Re: Valid Email Addresses

Anil_Babu_Samineni — Wed, 24 Apr 2019 12:46:58 GMT

expected result?

Re: Valid Email Addresses

mcoetzee — Wed, 24 Apr 2019 12:50:46 GMT

I would like to see what is a valid ID number and what not.

Re: Valid Email Addresses

arpitkharkia — Wed, 24 Apr 2019 12:54:22 GMT

I am not sure if you have used the right() function in the correct way.

Can you post the logic you need and some sample data?

Re: Valid Email Addresses

Anil_Babu_Samineni — Wed, 24 Apr 2019 13:00:13 GMT

Do you think this condition, Correct. For me it is quite wrong. As per your logic, Output will come like

Sample Data:

0000/00/00000 -------------> Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000' --------> True first part, Hence it will return 'Invalid'

NPO/000/0000 -----------> True --> Because, It is not the 13 Length --> Should return "Invalid"

0000/00/000(00) ---> true --> Because, It is not the 13 Length --> Should return "Invalid"

Below part is wrong for me. Can you explain what you are trying using these condition here?

Right (IDNumber, '/') Or Right (IDNumber, 'NPO' Or Right(IDNumber, '()'))

Valid ID Numbers

mcoetzee — Wed, 24 Apr 2019 13:24:26 GMT

Sample Data

I would like to check in my data, which of the ID numbers are qualifying as a valid ID number, but i have special characters in of which when checking they are not a valid ID number.

ID Number	Len	Reason
8106075006000	13	Valid
8106075006111	13	Valid
8111175006000	13	Valid
811(175006000	13	Invalid
811(175NPO	10	Invalid
8111175//000	12	Invalid
0///	4	Invalid
20016/0111/11	13	Invalid

Re: Valid ID Numbers

Anil_Babu_Samineni — Wed, 24 Apr 2019 14:10:05 GMT

Try this?

If(Len([ID Number])=13 and not FindOneOf([ID Number], '!@#$%^&*()/\_+-={}[]:;"''<>,./?~`'), 'Valid', 'Not valid')

Re: Valid ID Numbers

Anil_Babu_Samineni — Fri, 03 May 2019 13:45:39 GMT

If this resolved, Please close this thread.