
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to load only where (field) is number or numeric
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.)
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Steve,
You almost guessed it, it is IsNum().
Edit: I think it removes blanks as well, but not 0s.
Regards,
Michael

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
