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: 
vinaymaidargi
Contributor II
Contributor II

How to list ONLY Special Characters

Hello,

I have a list of Input first names like below

First Name:

Ajay

Ajay123$

Ajay$$$

Ajay#$%

Ajay@

$$$

$

%

&

*

@

^^^^^^^

----------

!!!!!!!

I need to pull only special characters. Even though other names has text with special character it should not display.

In this case, it should have the output as below

DESIRED OUTPUT:

First Name

$$$

$

%

&

*

@

^^^^^^^^^

-------------

!!!!!!!

I have tried to use Purgechar and Keepchar functions. In both cases, it will even list out the names which has text along with special characters.

Thanks,

Vinay

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_284807_Pic1.JPG

mapPurgeNumLett:

Mapping

LOAD Chr(RecNo()), ''

AutoGenerate 65535

Where IsNum(Chr(RecNo())) or Upper(Chr(RecNo())) <> Lower(Chr(RecNo()));

table1:

LOAD *,

    MapSubString('mapPurgeNumLett',[First Name]) as [First Name Out]

Inline [

First Name

André234%&/

Zoë§$

Seán()?

Sørina*#

____________

Ajay

Ajay123$

Ajay$$$

Ajay#$%

Ajay@

$$$

$

%

&

*

@

^^^^^^^

----------

!!!!!!!

André234%&/

Zoë§$

Seán()?

Sørina*#

];

hope this helps

regards

Marco

View solution in original post

11 Replies
Anil_Babu_Samineni

May be this?

LOAD [First Name] From Table Where Not FindOneOf(Upper([First Name]), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
evan_kurowski
Specialist
Specialist

Hello Vinay,

/* The most "sophisticated" programmatic way to approach this would be to load in the REGEX modules, but this requires module usage 
once you activate those though, that should allow leveraging character classes (i.e. isolate punctuation, alphanumerics, etc..)

Without REGEX, then there are other approaches, but PurgeChar/KeepChar can definitely complete the task */



Set vKeepString = ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789;


LOAD PurgeChar(ScrubField,'$(vKeepString)') AS ISOLATE_OUTLIER_CHARS RESIDENT MyUnfilteredInput;

MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_284807_Pic1.JPG

mapPurgeNumLett:

Mapping

LOAD Chr(RecNo()), ''

AutoGenerate 65535

Where IsNum(Chr(RecNo())) or Upper(Chr(RecNo())) <> Lower(Chr(RecNo()));

table1:

LOAD *,

    MapSubString('mapPurgeNumLett',[First Name]) as [First Name Out]

Inline [

First Name

André234%&/

Zoë§$

Seán()?

Sørina*#

____________

Ajay

Ajay123$

Ajay$$$

Ajay#$%

Ajay@

$$$

$

%

&

*

@

^^^^^^^

----------

!!!!!!!

André234%&/

Zoë§$

Seán()?

Sørina*#

];

hope this helps

regards

Marco

evan_kurowski
Specialist
Specialist

Let me see if I get this whole "ambush" straight.  Vinay posts a list of inputs that look like Ascii, but the riddle is that his alphanumerics lie north of chr(127) and are into Unicode territory (this had better be a surprise to Vinay, otherwise that was an important detail to omit).

Marco then recognizes this and uses it to show off a technique that will scan the Qv11.2 range of Unicode, under the premise any character that doesn't have both upper & lower is a flagged symbol, so long as it isn't a number.

Which reminds me.. since Qlikview is .net, when can we get regex out of the box?

vinaymaidargi
Contributor II
Contributor II
Author

Hi Marco,

Thanks for your response.

Your solution will work when the requirement is ANY SPECIAL CHARACTERS.

My requirement is to identify ONLY SPECIAL CHARACTERS. i.e., User enters the First name as only special characters like $,&,---,*** etc. These kind of entry needs to be flagged.

So, First name -----Text along with special characters eg : Ajay$&* or Vijay#@ is valid.

So, in your above example, Andre,Zoe,Sorina,Ajay etc are all valid entries.

Only the one with special characters to be flagged like $$$,%,*,@,^^^^^^ etc.

Hope you got my requirement.

Awaiting some solution, 😞

Thanks,

Vinay

Anil_Babu_Samineni

Is my response won't help??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vinaymaidargi
Contributor II
Contributor II
Author

Hi Anil,

This doesn't work either.

Or
MVP
MVP

That's the exact same thing, except you compare the length of TextIn and TextOut - if it hasn't changed, the name is all special characters. If it has, there's at least one valid character.

vinaymaidargi
Contributor II
Contributor II
Author

Hi Marco,

Thanks!!!!! your solution works and was able to achieve the desired output. Thanks a ton.

@Shoham, Thanks for the suggestion, and it helped me by comparing the length. and you are right about it ,if length has not changed, then it is special character, else it has valid character.

Thanks Anil and Evam for your responses as well.

Thanks guys

Cheers

Vinay