Filter NAN values

Hi - how can I calculate an average or count of a column while ignoring(!) NAN values?

nans

Best Rgds
Georg

@Georgio are your “ec” and “mu” columns calculated, or are these raw values? If they’re calculations, can you share them?

@Georgio just to add to Tadd’s response, there are functions such is_nan you can use to filter. Here’s a quick example:

+-----+
| a   |
+-----+
| 1   |
| NaN |
+-----+
Fetched 2 row(s) in 0.25s

 > select * from z2 where !is_nan(a); 
+---+
| a |
+---+
| 1 |
+---+

Hi @taddwood, raw values.

I am trying to aggregate this values to this:

lulb

But if I have only 1 “NaN” value in my column, the average calculation just show NaN

@Georgio I would filter out using the filter expression I indicated. That should avoid using the NaN values.

Please note this only works in impala/arcadia connections. If you are using other data connections types like mysql or postgres you should look for equivalent functions in their docs.

!is_nan(a)