3

my starting table looks similar to the following

Person 1, 75
Person 2, 48 
Person 3,
Person 4, 82
Person 5,
Person 6, 93 
...

I now try to include in following query a "where" statement to exclude entities that have no numeric value. This is what I currently have to show me the lowest 5 values of the set above and it works so far

=QUERY('DPS Transpose Tables'!D1:E29;"select D, max(E) group by D order by max(E) asc limit 5 label max(E)  ''";0)

How can I add something like this that works

=QUERY('DPS Transpose Tables'!D1:E29;"select D  where (E<>"" OR Is not NULL), max(E) group by D order by max(E) asc limit 5 label max(E)  ''";0)

Thanks a million in advance!

Rubén
  • 34,714
  • 9
  • 70
  • 166
Claus Maier
  • 95
  • 1
  • 2
  • 9

3 Answers3

3

Try 'where E >=0' like this:

=QUERY('DPS Transpose Tables'!D1:E29,"select D, max(E) where E >=0 group by D order by max(E) asc limit 5 label max(E)  ''",0)
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
1

You can also try 'WHERE IS NOT NULL'

Sometimes I find using the >= 0 does not get the desired results

=QUERY('DPS Transpose Tables'!D1:E29,"select D, max(E) where E IS NOT NULL group by D order by max(E) asc limit 5 label max(E) ''",0)

Chef1075
  • 2,614
  • 9
  • 40
  • 57
0

Use NOT Equal To and '' empty string with single '' combination

Where E <> ''

prajapati1010
  • 1
  • 2