topic Re: Detecting non-alphanumeric characters in QlikView

Detecting non-alphanumeric characters

Tue, 19 Feb 2013 12:24:19 GMT

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

Re: Detecting non-alphanumeric characters

Gysbert_Wassenaar — Tue, 19 Feb 2013 12:38:50 GMT

Try:

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

Re: Detecting non-alphanumeric characters

Tue, 19 Feb 2013 13:33:45 GMT

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

resident X

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

Re: Detecting non-alphanumeric characters

Gysbert_Wassenaar — Tue, 19 Feb 2013 13:39:41 GMT

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;

Re: Detecting non-alphanumeric characters

Tue, 19 Feb 2013 13:50:34 GMT

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

Re: Detecting non-alphanumeric characters

Wed, 20 Feb 2013 10:28:36 GMT

hi,

Just to let everyone know, I spotted my mistake.

thanks for your help everyone.