Using If(len(PurgeChar)) function to detect specific characters

[thanhng34]

I would like to create a flag to detect if the "Firstname" field ("Prénom" in french) contains specific elements, apart from alphabetic characters, '-' and spaces.
So I wrote the following expression:

If(len(PurgeChar([Prénom],'ABCDEFGHIJKLMNOPQRSTUVWXYZ- '))>0,1,0) as FlagErrorFN

Result : Some first names were identified errors (1) even though they does not contain any specific character (ANAS, MAVA, RAPHALLE, NAMA).

It seems like Qlik does not understand that I wanted to remove spaces with PurgeChar, and those names were identified errors because they contain some spaces before or after the name. For example, when I created a flag with Len(Prénom), it gave 5 for "ANAS", "MAVA", "NAVA" in stead of 4 so I think that these names contain spaces.

Could someone tell me what I have to write correctly to detect if the field "Prénom" does contain a specific character different of alphabetic charac, '-' and spaces ?

Thanks so much for your help,

Thanhng34

[Taoufiq_Zarra]

why not remove the space with trim() before purge

If(len(PurgeChar(trim(upper([Prénom])),'ABCDEFGHIJKLMNOPQRSTUVWXYZ-'))>0,1,0) as FlagErrorFN

[thanhng34]

Hi Taoufiq,

Thanks so much for your help. But it did not work because there are double names with spaces between two words (For example,  Jean Claude). The “trim” function can just remove spaces before and after the string, not between words. So those double names were detected as containing special characters ☹

Best regards;

[Kashyap_R]

Hi

Try this

IF(WildMatch(trim('prenom'),'*-*',' ')>0,1,0)

Hope  this helps 

Thanks

[jonathandienst · Accepted solution]

Your expression should correctly purge spaces at the start, middle or end of the string. Perhaps they are not simple ASCII spaces (chr(32)), they are instead, for example, a no-break space (chr(160)). Try this:

If(len(PurgeChar(Upper([Prénom]), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ- ' & chr(160))) > 0, 1, 0) as FlagErrorFN

Some databases pad out CHAR() fields with no-breaks, and Excel uses them for thousand spacing, among others.

[thanhng34]

Hi @jonathandienst ,

Your expression works well in my case. Thanks so much for your help ^^

Could you explain to me what is the difference between chr(32) and chr(160) ?

[jonathandienst]

Chr(32) is a normal ASCII space character (what you get when you hit the space bar).

Chr(160) is the unicode entity   (non-break space), which is used in word processors and spreadsheets to prevent a line breaking or wrapping on this space. For example, if you use spaces as  a 1000s separator, you don't want the number printed across 2 lines.