Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vinaymaidargi
Not applicable

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
Not applicable

Re: How to list ONLY Special Characters

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

11 Replies
loveisfail
Not applicable

Re: How to list ONLY Special Characters

May be this?

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

Life is so rich, and we need to respect to the life !!!
evan_kurowski
Not applicable

Re: How to list ONLY Special Characters

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
Not applicable

Re: How to list ONLY Special Characters

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
Not applicable

Re: How to list ONLY Special Characters

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
Not applicable

Re: How to list ONLY Special Characters

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

loveisfail
Not applicable

Re: How to list ONLY Special Characters

Is my response won't help??

Life is so rich, and we need to respect to the life !!!
vinaymaidargi
Not applicable

Re: How to list ONLY Special Characters

Hi Anil,

This doesn't work either.

Or
Not applicable

Re: How to list ONLY Special Characters

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
Not applicable

Re: How to list ONLY Special Characters

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