QUERY Function - Advanced Formulas in Google Sheets, Building Less Error-Prone QUERY Function
ฝัง
- เผยแพร่เมื่อ 2 ธ.ค. 2020
- Learn how to use advanced techniques in formulas in order to create less error-prone QUERY function statements in Google Sheets.
#query #advanced #gsuite
Inspiring me as usual. Never thought about the difference between array(collection of values) and range(collection of cells).
this video deserves 1billion likes and views. So helpful. thanks
Great content! I'll sometimes do concat and match on the headers to get the right columns (=QUERY({Data!A1:G},"SELECT Col"&MATCH("Date",Data!A1:1,0)&", Col"&MATCH("Customer/Store",Data!A1:1,0)&", Col"&MATCH("Sales",Data!A1:1,0)&" WHERE Col"&MATCH("Sales",Data!A1:1,0)&" > 8000",1)). Helpful when you can't select the columns individually (e.g. with imports). Your way looks simpler, and better if the headers could change.
Every single time I learn something new from you. Many thanks!
Dude I see you're still going strong that's awesome. I remember your AppScript tutorials from 2019 and you've always made your videos very clear and easy to follow along with. Just dropping some love man!
Hey, thanks!
I never tire of your videos and learn something new every time. Thanks once again.
Thanks a lot for this wonderful teaching
this is a great solution. thanks for sharing it
I've written a ton of sheet queries and never thought to do this to get around this problem of queries static references. Thank you!
You are a life saver man. I never get bored watching your videos.
This channel is wonderful! Thanks a lot
That was amazingly easy fix to some of my time consuming problems. Thanks a lot!
Brilliant! This will save me so many headaches. Thank you!
Excellent tutorial
Awesome your vides are always the best ones!!!
Simply brilliant!
Thank You, very helpful
Awesome, it solves a lot of my problems. Thank you.
Great! Thank you!!!
Thanks for make my life easier
💓
Makes sense. Better to take some more time in setting the query in such a way rather than spending time later to check how the error popped up.
Soooo clever !!!. Thanks
Thanks this is helpfu!
I like it!!!
amazing! você é foda, cara!
I really appeeciate your efforts sir but sir i did not find anything new in the last two videos of query function you have already taught this in your past videos which were amazing. Sir can you please make more videos on apps scripts thats challenging to learn. It is a humble request sir. I appreciate you efforts.
Hey can you tell me how to add extra column using query function showing from where the data is comming from the sheetname and row like the ROW() funtion you used with filter funtion in combining / making master sheet video
Could I actually add names for those ranges? Like date, store and sales? Would that work?
Yes, that will definitely work and preferred. Naming the ranges based on the column header makes it easier to visually index the query range as an array. I.e. QUERY({q_date, q_store, q_sales}, "SELECT Col1, Col2, Col3 WHERE Col3 > 7.00",1) would naturally state the array index as date(named range) = Col1, store(named range) = Col2, and sales(named range) = Col3
I replicated this same example and gave each column range the names of the headers. The leading "q_" is just my personal naming convention for stating the name is related to using for queries. However, instead of querying the entire range in the array QUERY({Data!A1:G1650}, "SELECT...",1) I called the ranges of concern QUERY({q_date, q_store, q_sales}, "SELECT...",1) E.g. Data!A1:A range is named "q_date", Data!C1:C range is named "q_store", etc. for modular global scope on the ranges to use wherever applicable throughout the workbook including other tabs. I'd even take it a step further and name the range of the entire dataset "Data!A1:G1650" to something I would name a database table like "tbl_customer_sales" so I could reference it in the query range section I.e. QUERY(tbl_customer_sales, "SELECT...", 1)
I know it's just not me, but it is definitely easier to build functions with named references rather names of the cell location. I also use this method to JOIN related data from other tabs rather than using the arrayformula({vlookup}) method.
Sure.
@@navigatormoore I find it much easier to work with named ranges when using query also, even if just to define the database range. Another nice benefit is that Sheets will autocomplete the range names. I like your "q_" prefix for that reason too, since you can effectively call up all your query column options by typing the prefix. I'm going to start using that too, so thanks for the idea.
This is super awesome. Why wouldn't I have known this before. Lol. By the way, i have one topic that might be very interesting to solve. After we have made a super complex Sheet system with dozen of query and Importrange from one sheet to others. Now I have a problem that I can't trace its dependencies of the original data. Now I'm super scared when I have to make some changes on the original set of data. It might causes other file somewhere to get an error like a chain reaction. 🔥🔥🔥
Have the same issue here. Excel has more visibility on data structure between files. Sheets with such functional as deadly weapon in a newbie hands
i’ve started building data index tabs where I list the name of the tab, it’s purpose, and where the original source data lives.
To your original point, data hygiene best practices is an important conversation to have
@@mattbrigidi I just started that also but the hard part is that I'm not the only one who touch the main source of data because we run at the company level so sometime it is hard to force everyone to make an update on the index table once they make changes.
That was a brilliant and concise visual explanation. Left me wondering if I can use the convention of no ending row in case more rows are added. Will need to try. i.e. Data!F1:F
Should work just fine.
Magic..
I have used the formula its kind of worked properly. My source data has been from 3 nos sheets.
Now the problem is that some cell in query sheet are found blank though there were data in source sheets. your help is highly appreciated. Thank you.
Because the data type is inconsistent of that particular column in data set.
Very inspiring.
I have an issue where I can't find the way to do it in Sheets. Can you make a video about this? In short...
I have a list of names and date of start working.
Now I want to show, on a different sheet, when I have a jubilee on this month.
You'll need FILTER TODAY MONTH functions.
Sir, u r lessons are so good
Sir, pls help me for fetching TXT and num value from query fn. I hav both value in single column. Query fn. Fetches NUM value but TXT value not. Pls help me.
Incredible! just amazing! This helped me a lot.
Is there a way to do this within an importrange since I am querying from one spreadsheet to another?
No, you would need to use a different technique.
@@ExcelGoogleSheets omg, so glad you answered, thank you!
I was thinking doing an import range separatey to all 3 of my sources I am trying to merge and then query them with arrays within my spreadsheet.
Do you have a better solution for this :o ?
If you have matching column names, you should be able to search and find the column.
@@ExcelGoogleSheets dont know if it helps now but I managed to do it with concat within the arrayformula and a textjoin before the arrayformula all in the same formula.
Dont know if its too complicated for what I am trying to do but it worked 🤷🏻♂️ jajajaja
Does the first parameter of query HAVE to be in an array in order to reference it as Col1, Col4…. And so on??
Yes.
Now you create a custom formula that maps the headers to the columns position (as range or matrix) and transcribe It to work with the default query formula.... And share with us (: Because i've never been able to work that out. Love all your SS videos btw.
:)
U did it! Thanks!
Vídeo maravilhoso, mas infelizmente não funcionou para mim. Os intervalos que estão dentro do das { } criaram uma coluna única. O erro "Não foi possível analisar a string de consulta para Função QUERY parâmetro 2: NO_COLUMN: Col2. Fórmula: =Query({Dados!A1:A100;Dados!B1:B100;Dados!C1:C100};"Select Col1, Col2";1). Observação: Aqui no Brasil usamos ";" no lugar da ",". Poderia me ajudar?
How do your sheets respond so fast? No processing time taken! Do you cut the videos?
Nice catch😁
I do cut videos, however in this instance it didn't affect the response time. That's how it works when you have fast enough internet connection and a fast enough computer without having too much going on in the spreadsheet.
@@ExcelGoogleSheets even this answer not for my question, but it was long time not seen your personal anwer like this appear in my notification pop up. I think I little miss it :)
@@ConsulthinkProgrammer I try to always answer comments as time allows.
@@ExcelGoogleSheets Thanks a lot Sir. I learn so much from you. I use it in my work and documenting it in my channel, to record what i've learn (almost) from you and some from other youtube channel :)
How to filter results by two dates?
Hmmm... interesting...what if I lock the range?..hmm.. let me try..
Tip on identifying errors in your SQL statement when it gives you a offset to the error within the SQL statement. New vid material? Example - i.imgur.com/x8Ua6VK.jpg
I use GSheets at work and this channel is so very helpful. I struggle with the formulas and this is my go to for answers.
I am looking for help with a problem i have from the subscibers.
Query Vs Vlookup
I replicated a problem i had in this spreadsheet: updated
docs.google.com/spreadsheets/d/1ROiZhKaEbjZMBjs0pA1Jg1vt3nQyNsptz2_tznykpPs/edit?usp=sharing
The original sheets are separate sheets. I used importrange to get the data from one spreadsheet to another.
I only want data from one colum to be imported into another spreadsheet based on the info in two column in a second spreadsheet.
I managed to do what I needed but i do not understand why query didnt work.
Also. I have one problem with a row that has a persons double surname.
You forgot to make it public.
@@ExcelGoogleSheets
docs.google.com/spreadsheets/d/1ROiZhKaEbjZMBjs0pA1Jg1vt3nQyNsptz2_tznykpPs/edit?usp=sharing
OK, so the reason you are having these issues is because on your Sheet1 names in the first column have an extra space after the last name.
@@ExcelGoogleSheets thx. i would not have even thought of looking there.