topic Removing character values and taking only numeric values from particular field in QlikView

Removing character values and taking only numeric values from particular field

Wed, 30 May 2012 07:35:15 GMT

Hi All,

I have one field as 'Plan_Id' which has got few initial values as characters and then numerical values as 001,002,003......

I need to bring only numeric values at UI level. Please suggest what function should be used.

Thanks,

Kirti

Re: Removing character values and taking only numeric values from particular field

Wed, 30 May 2012 07:48:28 GMT

Hi!

I think that you can use function KeepChar.

It works like this (copied from help file)

KeepChar(s1 , s2)

Returns the string s1 less all characters not contained in string s2.

Example:

keepchar ( 'a1b2c3','123' ) returns '123'

In your case s2 parameter should be like '0123456789'

Yours, Harri

Removing character values and taking only numeric values from particular field

Wed, 30 May 2012 08:15:32 GMT

Hi Harri,

My field has values in database like:-

Non

Nop

05A

H1B

001

002

003

004....

I need to remove first four and similar values which have characters and include only numeric values. Need to handle this in script but I am not sure which function will give me only numric values from one field.

Thanks,

Kirti

Removing character values and taking only numeric values from particular field

alvinford — Wed, 30 May 2012 08:18:43 GMT

Hi Try Using PurgeChar Function ..

Hope it Helps ...

Regards,

Alvin.

Re: Removing character values and taking only numeric values from particular field

alvinford — Wed, 30 May 2012 08:19:40 GMT

Hi Try Using PurgeChar Function ..

Hope it Helps ...

Regards,

Alvin.

Removing character values and taking only numeric values from particular field

swuehl — Wed, 30 May 2012 08:47:12 GMT

Maybe something like this?

LOAD

Field

FROM Table where not findoneof( lower(Field),'abcdefghijklmnopqrstuvwxyz');

LOAD

Field

FROM Table where len(purgechar(Field,'1234567890'))=0;

Hope this helps,

Stefan

Re: Removing character values and taking only numeric values from particular field

jonathandienst — Wed, 30 May 2012 08:58:11 GMT

Using PurgeChar could still leave a number if it was embedded in a string (eg A2B will return 2), which may not be what you want. I suggest that you use the IsNum(expr) function which returns true (-1) if the expr is numeric, or false (0) if not.

If(IsNum(expr), expr) As Value

Regards

Jonathan

Removing character values and taking only numeric values from particular field

Wed, 30 May 2012 09:01:08 GMT

Hi Stefan,

LOAD

Field

FROM Table where len(purgechar(Field,'1234567890'))=0;

is working perfectly. Thanks a lot.

Regards,

Kirti

Removing character values and taking only numeric values from particular field

swuehl — Wed, 30 May 2012 09:07:58 GMT

Good to hear, but I tend to complicate things from time to time and Jonathan pointed correctly to a much better solution using isnum():

LOAD

Field

FROM Table where isnum(Field);

Re: Removing character values and taking only numeric values from particular field

joseduque — Fri, 23 Dec 2016 16:30:51 GMT

if(Plan_Id=Plan_Id,Null(),replace(ltrim(replace(Plan_Id,'0',' ')),' ','0')) as Plan_Id