topic Re: Selecting only numbers from a field in QlikView

Selecting only numbers from a field

Mon, 20 Jan 2014 15:57:24 GMT

I need a table in QV containing a students year. The information is available in the SQL DB, but only as a number in a field containing class information.

Student_level

load

PersonID as %StudentID,

Year;

sql SELECT

PersonID,

left(Class,1) as Year

FROM Class

Where (Title like '%A' or Title like '%B' or Title like '%C' or Title like '%D' or Title like '%E' or Title like '%F' or Title like '%G')

and CHAR_LENGTH(Title) <4;

The SQL table looks like this:

PersonID, Class

1, 2A

2, 2A

3, 10B

4, 1G

5, 6C

and so on.

Unfortunately the above statement will return "1" as Year for both PersonID 3 and 4. Is there a way to strip away the characters, leaving just the numbers behind? Either in the SQL-statement or in the QV loading script?

I'm, as always, grateful for all help.

Re: Selecting only numbers from a field

hic — Mon, 20 Jan 2014 16:03:00 GMT

Yes, for instance

KeepChar(Class,'0123456789')

HIC

Re: Selecting only numbers from a field

giakoum — Mon, 20 Jan 2014 16:03:41 GMT

use Keepchar() function. try below mentioned syntex.

Ex: Keepchar('12ab98q', '0123456789')

Re: Selecting only numbers from a field

Mon, 20 Jan 2014 16:37:57 GMT

Hey Joachim,

Either Keepchar or Purgechar can be used.

Keepchar( 'Field' ,'0123456789')

Purgechar( 'Field', 'abcdefghijklmnopqrstuvwxyz')

Thanks

Re: Selecting only numbers from a field

Mon, 20 Jan 2014 16:41:08 GMT

Of course! Thank you.