topic Re: Filtering listbox on: Only numbers. in QlikView

Filtering listbox on: Only numbers.

Thu, 19 Mar 2015 13:10:31 GMT

Dear all*,

I think Ive got a straight forward and simple question, yet I could not find a corresponding topic.

I've got a listbox with expression: =Left(information, 2) selecting only the 2 first values of the information. Its all numbers, but there is some trash in the data source which are letters. (And a dropdown box?....)

How can I filter anything but numbers out of my listbox?

Thx in advance!

Re: Filtering listbox on: Only numbers.

tresB — Thu, 19 Mar 2015 13:16:07 GMT

Not IsNum(YourField) ?

And if you are looking to discard the sub-string, try using PurgeChar(), like:

PurgeChar(YourField, '0123456789')

Re: Filtering listbox on: Only numbers.

Thu, 19 Mar 2015 13:32:29 GMT

Hi thx for the suggestion, but its not working because of this I think:

The raw data is in this format:

00-00

01-34

02-24

TEXT

etc.

So when I set my expresion: =Left(information, 2) The following is shown:

TEXT

Now the TEXT is also unwanted. But If I say in my expresion:

=Left(YourField, 2)

=Not IsNum(YourField)

Nothing is shown, probably because it looks at the original raw data, which contains a "-" sign.

Got any alternative suggestions?

Re: Filtering listbox on: Only numbers.

sibideepak — Thu, 19 Mar 2015 13:35:36 GMT

Hi Dutchsky,

This may helpful!!

And try with this

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

Re: Filtering listbox on: Only numbers.

awhitfield — Thu, 19 Mar 2015 14:34:57 GMT

Hi Dutchsky,

try this out: if(IsNum(Left(DATA,2)),(Left(DATA,2))) Example attached

Andy

Re: Filtering listbox on: Only numbers.

Thu, 19 Mar 2015 14:40:08 GMT

Exactly what I needed! Thanks alot! Also Deepak Sibi thanks for your input, maybe I can use that another time. My example were not dates though.

Thank you