Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
krishna20
Specialist II
Specialist II

Junk Characters in Names List

Hi Folks,

I have junk characters (different characters not a standard ones) in one of my fields and which needs to separated as first and last names into two fields.

I need the count of those (Junk rows) error records also. Please find the below sample data and advise how can i achieve this.

I have used subfield but not getting expected result.

Names
-
, Amit Agrahari
, Anuradha Iyer
, Diamond Gong
, Hemant P. Deore
, Jules Keghie
, Vishal Prasad,
. Deepan.
. Harshana
. Martijn
\Remy Alexander
21 New Interns
A D A Ratnam
A.K. Pandey
T.Uday

 

 

Thanks

Krishna

Labels (2)
1 Solution

Accepted Solutions
Vegar
Partner
Partner

To filter out junk from your data you can use the PurgeChar() or KeepChar() functions.

I've used PurgeChar(Names, ',.-\1234567890@') in my attached example. 

In  a simple scenario where you only have two names separated by a blank ' ' you could use

subfield(Names, 1) as FirstName,
subfield(Names, 2) as LastName

...but in your example you have multiple names/letters with spaces between them and you will probably need a bit more complicated approach using mid() and index(). See my attached example. 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
rwunderlich

How do you define "Junk".  Anything that's not a letter?

-Rob

krishna20
Specialist II
Specialist II
Author

Hi Rob,

I mean the junk are  like ",.12345@".

Vegar
Partner
Partner

To filter out junk from your data you can use the PurgeChar() or KeepChar() functions.

I've used PurgeChar(Names, ',.-\1234567890@') in my attached example. 

In  a simple scenario where you only have two names separated by a blank ' ' you could use

subfield(Names, 1) as FirstName,
subfield(Names, 2) as LastName

...but in your example you have multiple names/letters with spaces between them and you will probably need a bit more complicated approach using mid() and index(). See my attached example. 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

krishna20
Specialist II
Specialist II
Author

Thanks Vegar :). That's worked with small modifications for my requirement.