4

I have an issue with my query where I have numeric cells and text cells that if all are empty I do not want to retrieve them however if anyone of the cells are not empty I want to retrieve that row.

I know that is not Null is used for numeric cells and <>'' is used for text cells.

For example:

Let's say the table looks like this in the original table of data. Sheet is called 'Data'.

             Column    |   A    |   B   |  C     |  D    |
             Cell Type | Number | Text  | Number | Text  |
             Row1      |  7     | Stuff |  5.33  | Sweet |
             Row2      |  8     | World |        | Hello |
             Row3      |  9     |       |        |       |
             Row4      |        |       |        |       |
             Row5      |        |       |  9.77  |       |

I want to be able to grab all the rows except Row4

If I use this query, it should work correctly with the cell types. Correct?

=Query(Data!A:D, Select D,B,A,C Where A Is Not Null OR B <>'' OR C Is Not Null OR D <>'')

I applied this to my example spreadsheet which uses the same principles as this query. Except that it only works on two of my sheets. The sheets that don't have much data to select; the query only grabs the headers. In my mindfor the second tab called DonSmithPool, it should be grabbing a few of the rows that have text comments and for the last tab called TeachPool it should grab 1 row.

Example Spreadsheet

5

the query only grabs the headers.

No, it grabs some rows, too. Scroll down... way, way down. There they are. Here's why you are hitting this.

  1. Some of your columns are completely empty. These are interpreted as string by default. Hence, is not null is always true for these, and you get the whole table.
  2. Since you order by B (ascending by default), the huge number of empty rows ends up on top, and leads you to conclude that the query returns nothing.

Unfortunately, there seem to be no way to declare the type of an empty column. This, combined with the "no nulls in string column" behavior, leads to people being driven crazy by not null.

To keep your sanity, either

  • make sure every column has at least one nonempty cell, with some value of the type you expect in that column.
  • or, use the less powerful but more straightforward filter command when it suffices for the task. Note that the output of filter can be fed into a query, to combine the best features of both.
  • Thanks for pointing out and explaining where my results ended up. – CamSyl Oct 8 '15 at 2:50
  • I can see how this would drive someone crazy as this should be easy if you have number values it should be treated like a number type if not defined otherwise. Seems like a glitch or something. – CamSyl Oct 8 '15 at 5:19
  • The type of a column is determined by the majority of its values. So, if you put even one numeric value there, it will be changed to numeric. The issue arises when you have absolutely no values in a column: the query has no basis to determine its type and guesses "string". – user79865 Oct 8 '15 at 5:21
  • 1
    I fixed it by making a test or dummy row that has a sample of the type of results I'm expecting from a form submission. I'll be making that a standard for all my spreadsheets from now on. – CamSyl Oct 9 '15 at 16:58
-2
  1. Empty is not equals to Not Null
  2. Numeric column cannot be empty as it can have value or null
  • 1
    You can see from the question that the author was perfectly aware of that. – user79865 Oct 8 '15 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.