How to do a right join (I think) in a query

Question

I have sets of data that I want to combine, but they must output to the same data weeks, and missing data should have nulls inserted. I am not particularly clever with SQL but I think I can explain it.

So I have dates, sales and products from the start of the year (just imagine there are many products):

Date	Sales
02-Jan-2021
09-Jan-2021
16-Jan-2021
23-Jan-2021
30-Jan-2021
06-Feb-2021	2
13-Feb-2021	3
20-Feb-2021	5
27-Feb-2021	3
06-Mar-2021	2
13-Mar-2021	1
20-Mar-2021	0
27-Mar-2021	2

I want my =Query to output the whole time data set, even if there are only limited data (as shown in the table above). My data will overall contain all the weeks, but each product might only start in the middle or might end once sold out. But I want my query to always return a full date range. In Access I would right join my weeks to my sales data weeks, and it would add in all the weeks with nulls.

How can I do that in sheets with Query (or other methods that hopefully doesn't involve a query per week)?

I'm glad to know you found your own solution. In your post, you mentioned "date, sales and products"; yet you showed us no products at all, no ranges, no explanation of how the sales numbers were calculated or from what data. So there would have been no way anyone here could have helped you. The most efficient and effective way to get help is to share a link with realistic sample data and manually entered results that show what you'd like done, as well as an explanation for how those results were determined. Just something to keep in mind for potential future posts. — Erik Tyler
My question only pertained to getting the blanks to show as well as the dates. The products (the only one missing) are arbitrary after I can fix it for 1 of these. — Pep Sakdoek

Pep Sakdoek Pep Sakdoek · Accepted Answer · 2021-03-29T16:30:28

FML what and adventure. Coming from here :

=ARRAYFORMULA(iferror(VLOOKUP(Sort(Unique(Query( SalesData!A:N," Select G",0)),1,true),QUERY(SalesData!A:N,"select G,J where (D = '" & product & "')",0),2,0),""))

You'll have to forgive this is just my data but I'll break it down, so you might be able to follow.

Sort(Unique(query(first one)),1,true) is the one that is your dates, I sorted it cause well, rather have it sorted. So this gives you a list of dates that you want to vlookup into.

Then you do another query that returns your results in a 2d table: Query( 2 columns selecting your dates first). Then just in your arrayformula vlookup list, you just want to give "" when it reaches a value without something in your 2d table.

Amazing. Happy to get it to work... Now lets see if I have 1000 of these babies in my sheet if it survives.

How to do a right join (I think) in a query

1 Answers