Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Detecting non-alphanumeric characters

Hi,

I am using

len(purgechar(fieldname, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'))

to flag an error if there are anything apart from letters or numbers in the field.

However, if I set the above expression >0 it flags all the fields as an error. If I set it >1 it miss out on fields that ONLY contain special characters.

Does anyone know what I am doing wrong? Has anyone had this error before?

Cheers

5 Replies
Gysbert_Wassenaar

Try:

if(len(trim(purgechar(fieldname, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890')))=0,0,1) as HasSpecialChars


talk is cheap, supply exceeds demand
Not applicable
Author

hi,

that is a way I have tried it and it does seem to locate the special characters. however I am trying to use this statement as one of the conditions that has to be meet in a where statement. my code looks like this;

load

     A,

     B

resident X

where A>1 or  len(purgechar(fieldname, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'))>0;

Gysbert_Wassenaar

Since you're loading from a resident table maybe you can create the field HasSpecialChars in that table and then do something like:

load A,B

resident X

where A>1 or HasSpecialChars = 1;


talk is cheap, supply exceeds demand
Not applicable
Author

hi,

I see your point and I have done it that way.

however I require to do this test for several fields in the same table, so I was hoping I didnt have to create new names for all of them. One thing I forgot to mention, the way I have done it above works with most fields and is only failing on 1 field.

I will resort to your method if I fail to find a way around the problem though.

cheers

Not applicable
Author

hi,

Just to let everyone know, I spotted my mistake.

thanks for your help everyone.