topic Re: How to get only character data from complete name in QlikView

How to get only character data from complete name

Tue, 24 Sep 2013 07:44:19 GMT

Hi,

I have one column values in sql table as

Name= 32456 Mr Adam Jones

I need to get Name= Mr Adam Jones in Straight table. Could anyone help me how to write expression or script to get obly name part.

Thanks.

Re: How to get only character data from complete name

Tue, 24 Sep 2013 09:13:45 GMT

Hi,

I am assuming that all the values have a 5 digit numerical value preceeding a name. You can use Mid function as follows:

Load * Inline [

Name

32456 Mr Adam Jones
32247 Mr Brown Smith
] ;

Then use the following function and add it as a calculated dimension:

Mid(Name,7)

Also, you can use the 3rd parameter in Mid() function, if you need to specify the length of the string to be returned.

Hope this helps!

Re: How to get only character data from complete name

jagan — Tue, 24 Sep 2013 09:14:43 GMT

Hi,

Use this

=PurgeChar(Name, '1234567890')

Regards,

Jagan.

Re: How to get only character data from complete name

Tue, 24 Sep 2013 09:20:07 GMT

In the script try

mid(Name,(1+Index(Name,' '))) as Name

Re: How to get only character data from complete name

Tue, 24 Sep 2013 09:25:37 GMT

Purgechar('32456 Mr Adam Jones' , '0123456789')

The opposite is keepchar.

Re: How to get only character data from complete name

amars — Tue, 24 Sep 2013 09:55:57 GMT

Hi Amelia,

Use Either of

PurgeChar(Name, '1234567890')

KeepChar(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz').

You can use it in either script or expressions. Better to use it in script and create a field with all texts.

Thanks..

Re: How to get only character data from complete name

tresB — Tue, 24 Sep 2013 10:08:40 GMT

Use PurgeChar() like:

=PurgeChar(NameField, '0123456789')

Re: How to get only character data from complete name

AbhijitBansode — Tue, 24 Sep 2013 10:24:02 GMT

Use PurgeChar function

PurgeChar (Name,''0123456789)

Re: How to get only character data from complete name

Tue, 24 Sep 2013 10:24:27 GMT

I have provided two ways to get rid of the number. Please see attached

Re: How to get only character data from complete name

israrkhan — Tue, 24 Sep 2013 11:04:38 GMT

Hi,

if you know that numbers are fix length like above 32456 (five digit).

than you can use Right function in script.

Re: How to get only character data from complete name

Tue, 24 Sep 2013 11:08:23 GMT

Please can anyone help me

Re: How to get only character data from complete name

Tue, 24 Sep 2013 11:09:57 GMT

Use PurgeChar, as below;

Load

PurgeChar(Name, '0123456789') as Name

Resident Name;

Let me know how you get on.

Regards

Steve

Re: How to get only character data from complete name

m_woolf — Tue, 24 Sep 2013 11:11:26 GMT

Look in help for Purgechar()

Re: How to get only character data from complete name

Tue, 24 Sep 2013 11:22:36 GMT

Hi Amelia,

Please find attachment.

Hope this will help.

Regards,

Adeel

Re: How to get only character data from complete name

Kushal_Chawda — Tue, 24 Sep 2013 11:23:06 GMT

try:

mid('32456 Mr Adam Jones',index('32456 Mr Adam Jones',' ',1),index('32456 Mr Adam Jones',' ',-1))

Re: How to get only character data from complete name

qlikpahadi07 — Tue, 24 Sep 2013 11:29:18 GMT

=purgechar ('32456 Mr Adam Jones','1234567890')

Re: How to get only character data from complete name

sushil353 — Tue, 24 Sep 2013 11:31:32 GMT

Hi,

Use purgechar ()

purgechar ( 'a1b2c3','123' ) returns 'abc'

HTH

Sushil

Re: How to get only character data from complete name

Andrea_Ghirardello — Tue, 24 Sep 2013 12:05:30 GMT

You can achieve your needs in several ways:

1. using KeepChar or PurgeChar functions.

e.g. trim(purgechar ( Name,'0123456789' )) returns 'Mr Adam Jones'

2. if the prefix has a fixed lenght you can use the right function

e.g. right(name,len(name)-6) returns 'Mr Adam Jones'

Re: How to get only character data from complete name

Anonymous — Tue, 24 Sep 2013 12:22:43 GMT

Amelia:

i think the simplest way to achieve it is :

Trim(PurgeChar(Name,'0123456789'))

where Name is a Fieldname or Variable

Regards

Re: How to get only character data from complete name

Tue, 24 Sep 2013 12:28:26 GMT

You can use Purgechar function

Purgechar('apple123','123') will return 'apple'