V
V
vrplayk2020-11-22 22:37:02
Google Sheets
vrplayk, 2020-11-22 22:37:02

How to fix number and date filter errors in QUERY?

String data is filtered in QUERY normally. As soon as it's numbers or dates, the #N/A error occurs - the query did not return any results.

There is a wide table
. Download yourself a Google drive for a test.

spoiler
Она заполнена данными, среди которых есть колонки со временем и числами, которые дают сбой, при попытке их задействовать в фильтрации.
На соседнем листе с таким листом "Test", т.е. на листе "Filter", есть система фильтрации и вывод результата в колонке "BC".

В первой паре листов Test и Filter, формула не задействует каверзный фильтр колонки M, названной SEED с числами. Можно посмотреть как оно работает. Во второй паре задействует... Там формула дописана в конце этим фильтром и не работает.

В третьей паре есть моё временное решение изменять числа на строковые данные, добавляя к числу слово в конце. Так это работает. Этим костылём не воспользуешься при динамических данных, добавляемых через гугл-форму.

Что я упустил? Попытка убрать одинарные кавычки, чтобы обозначить фильтру что это числа, у меня не увенчались успехом. Формула сложноватая.

At the end, I left the "FilterWrong" sheet for exercises with a compact filter system that works until you add a date column filter to the formula. They have the same problem.

Here is the formula in an easy-to-understand form. Before using, you will need to reduce it to a single line.

=QUERY(TestWork!A4:AJ; 
  "select * where " 
& ЕСЛИ(СУММПРОИЗВ(A11:A15); " (C = '" & JOIN("' or C = '";FILTER(B11:B15;A11:A15)) & "')";" C <> '@@@@@'") 
& ЕСЛИ(СУММПРОИЗВ(A2:A5); " and (G = '" & JOIN("' or G = '";FILTER(B2:B5;A2:A5)) & "')";" and G <> '@@@@@'") 
& ЕСЛИ(СУММПРОИЗВ(C2:C25); " and (M = '" & JOIN(" or M = ";FILTER(D2:D25;C2:C25)) & "')";" and M <> '@@@@@'") 
& ЕСЛИ(СУММПРОИЗВ(E3:E); " and (J = '" & JOIN("' or J = '";FILTER(F3:F;E3:E)) & "')";" and J <> '@@@@@'")

)


Adding the next formula layer, filtered by dates, will result in an error
& ЕСЛИ(СУММПРОИЗВ(G3:G); " and (B = '" & JOIN("' or B = '";FILTER(H3:H;G3:G)) & "')";" and B <> '@@@@@'")

Also, if the data of column M are still numbers, then the corresponding line added before that will also bring down the result.

How to fix number and date filter errors in QUERY?
What should be paid attention to in such misunderstandings?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question