Learn how to rename columns using label clause in Google Sheets QUERY & format results as number, currency, different date types, rename and format multiple columns and much more.
Hello! Thank you for video. I've a question about "options no_values" clause. Could you help me? I have 2 very large tables with several thousands of rows and only 2 columns each. First column is 'datetime' and the second is some parameter. In the table A datetime had been recorded each 5 minutes, and in table B - each 20 minutes. I want to merge these tables into one with datetime every 1 hour.. Something like this: Datetime (YYYY-MM-DD HH:00:00) AVG(ParameterA) AVG(ParameterB) So what’s am I doing? 1. Getting data from table A with query: Select MIN(A), AVG(C), Year(A), Month(A)+1, Day(A), Hour(A) group by Year(A), Month(A)+1, Day(A), Hour(A) Order by MIN(A) Format MIN(A) 'YYYY.MM.DD HH:00:00' 2. It's give me exactly that I need! Every row have been grouped by hour and everything seems to be ok. 3. Than I’m getting data from Table B with the pretty much the same query. 4. And after all I’m trying to merge data by VLOOKUP (hour-by-hour). 5. And here comes the problem! Although my datatime columns looks very nice and clean (because of format) it has different Min(A) values, because data was written in different minutes of hour. So I have cells in Tables A&B looks like “2017.06.15 01:00:00”, but if I’m checking the values of these cells, it is “2017.06.15 01:04:00” and “2017.06.15 01:09:00” for tables A and B. And because the values is different, VLOOKUP is not working. ( 6. I’ve found information about “options no_values” clause, but it is not changing anything in my case… What I’m doing wrong? Thank you!
Hi Very helpful, thank you! I have an issue where I am using query to pull names from one table into a secondary table but when a new name is added to the first table and it pulls through to the secondary table, it doesnt reconise the new rows and doesnt auto format? Why is this? Can I fix it?
@Learn Google Spreadsheets How to have total sum below the transactions? Also is it possible to have sub-total below the transactions called using query
Awesome Playlist. I've had so many "AHAA!" moments watching your courses. Excellent! I have a problem where I want to report the ONLY the most profitable sales number out of 3 categories of items, made by 10 different brands, all of which have sales in the same set of 20 cities. So for Los Angeles maybe NIKE's most profitable sales category is shoes at $3m and Ralph Lauren's most profitable sales category is hats at $1.2m, but in Dallas NIKE's most profitable category is shirts $2m and Ralph Lauren's most profitable sales category is shoe's at $4m. I could make a report that shows all of the categories and just visually locate the most profitable one, but I want to only show the most profitable one and ignore the others. The brands would have different categories for each city because the demographics are different. Any guidance is appreciated. I'd like to avoid writing a script with some kind of loop.
Sound like you need to create one query to get your profit by category and then use it as a subquery to get the most profitable ones out of that. Watch my Query Subquery video for to see how you run subqueries.
Hi. Great content btw. Please keep it coming :) My question. =QUERY('Booking Sheet'!$A$3:$F,"select B where D='"&E14 & "' limit 2",0) Let's say you have set your display results limit to 2 because you know in some cases there is more than one match. How do I get the results to display horizontally in the next cells instead of below each other? I'm asking you because I actually had something like this a few years ago but can not for the life of me remember how I did it. Your help would be greatly appreciated.
Buen video. Pregunta como puedo hacer 2 filtros en el query, tengo que filtrar por fecha y por letra QUERY(plaguicidas!A4:T;"select A,H,J,K,P,S,R,Q WHERE E='"&B2&"' AND Q>1 ORDER BY A,S";-1)
hello i have a problem labeling a column header with a Date teken from a cell data validation, the result is a number and not a year this is my code =QUERY(TB_vendite;"SELECT sum(I) where month(A)+1 ="&MESE($B$3)&" and year(A)="&ANNO($B$2)&" group by M label sum(I) '"&($B$2)&"'") where b2 is the cell data validation....the data validation contain 2023 but the label return 44563 i tryied to format by menu.....but nothing...helpe me please
Hello. I trying some SQL like 'SELECT A, B/100,1 GROUP BY A', and "100,1" is a number in a cell. The result is 3 collumns. First is A, the second is "quotient(sum xxx())" and the third is "yy()" where yy is the decimal part of the B/100,1... I solve using 'B/" & TRUNC(100,1) & ' ...not the best.
Amigo, preciso de ajuda: " =QUERY( QUERY('DF_Financeiro!'!A2:P;"Select Year(A),Month(A)+1,N,P,O Where Year(A) = 2023";1); "Select Col1,Col2,Col3,Sum(Col4),Col5 Group by Col1,Col2,Col3,Col5 Order by Col1,Col2,Col3";1) " Essa função acima funciona muito bem... Mas gostaria que o "Month(A)+1" desse como produto o nome do mês por extenso (Janeiro, fevereiro...) porém quando uso " Format Month(A)+1 'mmmm' ele simplesmente falha e me trás apenas Dezembro e Janeiro em meses que não deveria.
Can you just help me to correct my Formula, I am getting error when i am putting functions of label =query(A8:AG,"Select A,J,D,U,G,I,K,R,Count(Z),Count(AE),Count(AG), Count(Z)-Count(AE), Count(Z)-Count(AG) where X='SW' group by A,J,D,U,G,I,K,R, lable Count(Z)-Count(AE) 'Jt Count Fitup Bal "',1)
At 6:58 you show YEAR(A) parameter and get what you want. When I try QUERY fails (#VALUE!) and I am told A does not have a DATE or DATETIME format. But it does: I checked. And regular function =YEAR(A) works fine in my sheet. What am I missing?
@@ExcelGoogleSheets Thanks for quick response :-) I have triple checked that entire column is only dates and in DATE format (except for first row which has the Column label which is why QUERY has 1 as last parameter). No blanks in column. Each cell contains MM/DD/YYYY formatted numbers. And =MONTH() does work correctly for the entire column. I must be blind to something subtle or basic? Btw: I am learning so much from your many videos: Thank you!
