Mar 1, 2021
Flexible QUERY function - help with an MVP
Hello. I need some help with a QUERY formula that allows me to do dynamic searches, using as many arguments as necessary to obtain the result, but not being mandatory to use all of them.
To explain it better, let me show you how this spreadsheet will be used, which will serve as an MVP for an automated photosystem I'm building (hardware + software).
Imagine that I need to photograph a can of Budweiser. To get the best shot, I need to test a light scheme on the product until I find the ideal set - one that brings me the right brightness and volume in the image.
Well, the next time a similar beer can arrive to be photographed, I would like to have this light set stored so that I can quickly set the ideal light without wasting time. This is the hypothesis to be validated.
So, let's see, the ideal light for a Budweiser can be used for all products that have the same characteristics (which we call attributes):
- Metal
- Can
- Round
- Soft colors
- Reflective material
What I need is to be able to filter which preset is ideal to use at that moment, allowing me to see all the options available as I choose the attributes:
For example, I want to see all the saved presets for metal products > cans > round - so I can decide which one most closely matches the situation I need.
=ArrayFormula(QUERY({Presets_database!B1:AK};"select * where Col2 matches """&K9&""" AND Col3 matches """&L9&""" order by Col1 desc ";1))
However, my knowledge of QUERY does not allow me to filter all metal products that are round, as the "Type" attribute has not been provided - causing the search to return blank:
=ArrayFormula(QUERY({Presets_database!B1:AK};"select * where Col2 matches """&K9&""" AND Col3 matches """&L9&""" AND Col4 matches """&M9&""" order by Col1 desc ";1))
I have tried OR statement but that's not it yet. I think what I need is a mix of AND & OR that allows me to use or not an attribute to get the result. Or, in other words, use any attribute I want to display the result.
Is that possible?
Another example of a search that would be excellent to have available: I want to see all presets for beer, typing on
U9 (no matter if it's a glass or a metal package).I hope I managed to be clear.
QUERY() formula is on
J11.Here:
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Mar 1, 2021
Mar 2, 2021
Okay, Marcos, I added the last check for the product name, as a full string match.
I had been thinking that you might want to just enter a keyword, like "Food", in that criteria field, and it could "match" any product that had the word food in its description. Either way is possible, so you can think if you want it different.
Let me know if there is anything else, or if you have any questions about this.
I'll check back later today...
Cheers,
Gill
Last edited Mar 2, 2021
Original Poster Marc BR marked this as an answer
Mar 2, 2021
Hi Marc BR,
I love using queries in this dynamic way!
Have a look at this approach. I've cheated and added an extra row 10 underneath your attributes, to create the query criteria, but this could be moved away completely, even to a hidden tab if you preferred. It helps me to see it while debugging the formula. They are highlighted in yellow in the image below.
If you make a sample of your sheet shared so anyone can EDIT it, then I, and others, can demonstrate possible solutions for you, on a copy of your sheet.
The formulas in K10 to T10 are basically:
=IF(K9="";"";" and Col2 ='"&K9&"' ")And the main QUERY becomes:
=IF(COUNTA(K9:Z9)=0;""; ArrayFormula( QUERY({Presets_database!B1:AK}; "select * where Col2 <>'' "& K10 & L10 & M10 & N10 & O10 & P10 & Q10 & R10 & S10 & T10 & U10 & " order by Col1 desc ";1)))I haven't yet included the "matches" for your product description, since I'm not clear if you want a keyword search there, or an exact product description match.
Let me know if this helps.
Cheers,
Gill
When you've received a response that answers your question, please observe these forum courtesies;
• Leave your demo sheet shared as part of this solution's archive,
• Click Recommend on the post that best addressed your question, and
• Post again soon!
Original Poster Marc BR marked this as an answer
All Replies (6)
Hi Marc BR,
I love using queries in this dynamic way!
Have a look at this approach. I've cheated and added an extra row 10 underneath your attributes, to create the query criteria, but this could be moved away completely, even to a hidden tab if you preferred. It helps me to see it while debugging the formula. They are highlighted in yellow in the image below.
If you make a sample of your sheet shared so anyone can EDIT it, then I, and others, can demonstrate possible solutions for you, on a copy of your sheet.
The formulas in K10 to T10 are basically:
=IF(K9="";"";" and Col2 ='"&K9&"' ")And the main QUERY becomes:
=IF(COUNTA(K9:Z9)=0;""; ArrayFormula( QUERY({Presets_database!B1:AK}; "select * where Col2 <>'' "& K10 & L10 & M10 & N10 & O10 & P10 & Q10 & R10 & S10 & T10 & U10 & " order by Col1 desc ";1)))I haven't yet included the "matches" for your product description, since I'm not clear if you want a keyword search there, or an exact product description match.
Let me know if this helps.
Cheers,
Gill
When you've received a response that answers your question, please observe these forum courtesies;
• Leave your demo sheet shared as part of this solution's archive,
• Click Recommend on the post that best addressed your question, and
• Post again soon!
Original Poster Marc BR marked this as an answer
Mar 2, 2021
What a nice approach! Create strings using IF open a few ways of possibilities for me, not just for this particular spreadsheet. I appreciate that.
Would be great if I can use a keyword-based search (on
U9) but I'm afraid I do not get what you mean by using 'matches' for that.Edit option is on now if you want to take a look.
Thanks a lot!
Okay, Marcos, I added the last check for the product name, as a full string match.
I had been thinking that you might want to just enter a keyword, like "Food", in that criteria field, and it could "match" any product that had the word food in its description. Either way is possible, so you can think if you want it different.
Let me know if there is anything else, or if you have any questions about this.
I'll check back later today...
Cheers,
Gill
Last edited Mar 2, 2021
Original Poster Marc BR marked this as an answer
Mar 4, 2021
Mar 14, 2021
I made some layout updates but I don't know what exactly it is possible to bring with the inclusion of keywords so I prefer to wait before finishing this part.
Having a keyword-based search will definitely help a lot. If it is possible to merge the search for keywords with attributes somehow, it will be state of the art.
Thanks!
Mar 14, 2021
Hi Marc,
Sorry to hear about the health issues - good luck.
I finally got back to this, and think I have the keyword search working. The formula in RefData AE2 will create the query search string for all of the object keywords entered, to search in Presets column K, for the keyword(s) entered in Search T1. This is in addition to any other attributes selected.
The list of possible keywords you can search for is built by parsing the object keywords column into every unique word. I think this makes sense, but it could be done other ways.
If you like, you can ignore the dropdown and enter multiple keywords in T1, separated by a comma, and it will do an "OR", and find objects that match any of the keyswords.
The formula is:
=IF(Search!T1="";""; IFERROR( IF(FIND(",";Search!T1); " and UPPER(Col10) matches '.*" & UPPER(SUBSTITUTE( SUBSTITUTE(Search!T1;", ";".*|.*");" ";"")) &".*' ";); " and UPPER(Col10) contains '" & UPPER(Search!T1) & "'"))Let me know if you see any issues.
Cheers,
Gill