topic Re: How to load only where (field) is number or numeric in QlikView

How to load only where (field) is number or numeric

stevelord — Mon, 15 Apr 2013 16:25:19 GMT

Hi,

I had an issue where 0 and blank values were pulling down calculated averages that included records with no data. (I.e. someone stuck a 0 in height when they didn't have the information, or left it blank, and QV pulled in the record and factored the 0 height into the average.)

I think I solved the 0 and blank/null issue with this:

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1) WHERE TestValue>0 AND TestValue<>'' ;

(The '' is actually two apostrophes with nothing between to hopefully exclude blank field data.)

Now if someone wrote the word n/a, or blank, or some other non-numeric value in the testvalue field, I'd want to exclude that record/row from the load as well.

Can someone show me what I'd add to my WHERE statement above to accomplish that? I took a guess at AND IsNumber() and some other stuff, but haven't hit on it.

Thanks QlikCommunity!

-Steve

PS> My WHERE statement did kick out 0s and let results of my weighted average formulas go up to where they should be. (There can be no 0s in these testvalues as they are related to biometrics and someone would have to be a ghost or a robot to have a 0 value for any particular thing.)

Re: How to load only where (field) is number or numeric

Anonymous — Mon, 15 Apr 2013 16:43:30 GMT

Steve,

You almost guessed it, it is IsNum().

Edit: I think it removes blanks as well, but not 0s.

Regards,

Michael

Re: How to load only where (field) is number or numeric

Gysbert_Wassenaar — Mon, 15 Apr 2013 16:45:34 GMT

You could try ...WHERE TestValue;

If TestValue is a number other than 0 it will evaluate as True and otherwise False. So only records with a TestValue that's a number other than 0 should be loaded.

Re: How to load only where (field) is number or numeric

stevelord — Mon, 15 Apr 2013 17:04:13 GMT

Thanks, the WHERE TestValue; did the trick!

I tested with dummy data using 0, blank, and the word ‘blah’. Without the statement, the values were low and weighted with the unwanted 0s. With the where statement, the unwanted 0/blank/blah were not loaded and not weighed in.

-Steve