Apr 26, 2019
Query giving "empty output" error when it shouldn't.
https://docs.google.com/spreadsheets/d/1MB-Y9UDDyv-VRwK-JkYQL3jzJfa9c1N-99Pu5K0_WAw/edit#gid=1801852794
I did have it working fine, but when I change the "Teacher Conflicts" sheet to do an import range, everything stopped working. Thanks in advance!
Ben
Ben
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Apr 26, 2019
Hello Benjamin,
The problem with the query stems from the empty column H. Query interprets empty columns as text columns, and it does not perceive empty text strings as valid dates. It's an odd situation you've got there!
Using the N() function I can convert all the columns Dates and Blanks alike to numeric values. And the extra bonus of this technique is that you can do straight value-to-value comparisons against your criteria date, like 4/1/2019, without all the Query overhead of Text() formatting. THANKS MATT KING FOR THINKING OUTSIDE THE BOX on this issue.
Please see cell G4 on your Events tab for a working formula. Double check for accuracy, and post back with questions.
NOTE: Formula edited since posting
=Arrayformula(query({N('Teacher Conflicts'!$A$3:$A),'Teacher Conflicts'!B3:B, N('Teacher Conflicts'!$D$3:$H)}, "select Col2 where Col1 != "& $E$4 & " AND Col3 != "& $E$4 & " AND Col4 != "& $E$4 & " AND Col5 != "& $E$4 & " AND Col6 != "& $E$4 & " AND Col7 != "& $E$4 ,0))Best,
Lance
Last edited Apr 26, 2019
Diamond Product Expert Yogi Anand recommended this
Original Poster Benjamin Yergler marked this as an answer
Apr 29, 2019
Your dates in column H were not true date entries ... those were TEXTUAL
addition of +0 coerced them from TEXT looking like NUMBERS into true NUMERICS
things like entering dates that are not true numbers are a real headache ... cause unexpected errors and hard to troubleshoot
I hope this helps
Last edited Apr 29, 2019
Original Poster Benjamin Yergler marked this as an answer
Apr 29, 2019
The problem is with your dates in 'Teacher Conflicts'!D2:H10
see details and fix in sheet yogi_Events added in your spreadsheet
Original Poster Benjamin Yergler marked this as an answer
Apr 27, 2019
whenever you use a formula on a whole array of cells, (well most of the time), you have to put an ArrayFormula() wrapper around the whole thing.
So try that...
=ARRAYFORMULA(TRIM(IMPORTRANGE(....
Cheers,
Matt
Matt
Original Poster Benjamin Yergler marked this as an answer
Apr 26, 2019
Edit: in addition to very fine contribution from Lance
instead of
=query(T!$A$3:$H, "select B where A != date '"&TEXT($E$4,"yyyy-mm-dd")&"' and D != date '"&TEXT($E$4,"yyyy-mm-dd")&"'and E != date '"&TEXT($E$4,"yyyy-mm-dd")&"'and F != date '"&TEXT($E$4,"yyyy-mm-dd")&"' and G != date '"&TEXT($E$4,"yyyy-mm-dd")&"'and (H != date '"&TEXT($E$4,"yyyy-mm-dd")&"' or H is not null) ",-1)
use
=query(T!A5:H, "select B where (A != date '"&TEXT(E4,"yyyy-mm-dd")&"' or A is null) and (D != date '"&TEXT(E4,"yyyy-mm-dd")&"' or D is null) and (E != date '"&TEXT(E4,"yyyy-mm-dd")&"' or E is null) and (F != date '"&TEXT(E4,"yyyy-mm-dd")&"' or F is null) and (G != date '"&TEXT(E4,"yyyy-mm-dd")&"' or G is null) and (H != date '"&TEXT(E4,"yyyy-mm-dd")&"' or H is null) ",0)
as illustrated in attachment
Last edited Apr 27, 2019
Diamond Product Expert Yogi Anand recommended this
All Replies (17)
Apr 27, 2019
Hey Lance and Yogi!
And hello Ben,
Lance mentioned your post to me last night and it looks similar to something I've seen done so i wanted to put it up there as an option for you in case you're interested. On the MK.Idea tab, i've put my FILTER() which is a little different than the QUERY() Lance and Yogi wrote, but a similar idea. I placed it in K2, but you could put it anywhere of course.
Lance mentioned your post to me last night and it looks similar to something I've seen done so i wanted to put it up there as an option for you in case you're interested. On the MK.Idea tab, i've put my FILTER() which is a little different than the QUERY() Lance and Yogi wrote, but a similar idea. I placed it in K2, but you could put it anywhere of course.
I based it on a date that is generated dynamically as the most recent date written in column B. The idea is that as you added an event name and then a date, the dropdown cell would adjust to filter for the most recent date's availability and you could just choose an available teacher from the dropdown.
In the version that i helped build for someone else, he also wanted the employees in the dropdown listed in reverse order of how recently they'd had another event assigned to them. That would certainly be possible as well, but i didn't want to go overboard :)
Cheers all,
Matt
Matt
Apr 27, 2019
Thanks so much for working on this! I knew posting it would make the formula and process a lot cleaner. It looks like the converting to numbers idea works pretty well, as does the Filter idea. The MK tab is a good approach, too, but I don't think I need it that fancy. I'm doing this for a colleague in my school who I think would freak out seeing all the error messages.
Can you guys tell me the difference between Matt's filter method and Lance's numbers method? Is there a situation where one is better than the other (for instance if a form response leaves all the conflict columns blank)? Why does the query work better saying Col1 instead of A? Is that because now they are all numbers?
Thanks again, guys! Much appreciated!
Ben
Apr 27, 2019
I do want to present in the following yet 
another approach -- formula in cell F4
another approach -- formula in cell F4=ArrayFormula(query(T!B5:B,"select* where not '"&textjoin("~",1,if(T!A5:Z=E4,T!B5:B,))&"' contains B"))
as illustrated in attachment