Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Google Query Function Incorrectly Filtering Data

I am having an issue where Google Query is incorrectly filtering columns based on data values in a specific column.

The link to an example of the issue is here.

On the "Roster" tab in the highlighted red cells, there is a cell formula that queries the "Database" tab. There should be no returned values as it says no rows with "PL" in Col1 except it returns a row with "PL" in Col1. What could be the reason for this?

Most of the data has been cleared for privacy reasons and replaced with John Doe however on duplicate copies of this the query works but on this version, it does not work.

Solution

  • what you experience is the result of not defining the 3rd parameter in QUERY syntax that defines the number of header rows. by skipping it QUERY obeys selective implementation, in other words, it decides on its own to account in header rows or not based on the dataset.

    =QUERY(SORT(Database!$D$2:$M, Database!$G$2:$G, 1, Database!$M$2:$M, 1),
 "select Col8 where Col1<>'PS' and Col1<>'PL' and Col3='2'", 0)

    0