Google Sheet join and aggregate

Question

Given table A:

id	value1	value2
1	0.33	0.25
2	0.4	0.7

and table B:

date	id	quantity
01/08	1	0.25
01/08	2	0.45
02/08	1	1
04/08	2	0.7

I would like to create a table which joins A and B by id and sums all value1 and value2 for all dates like this:

date	value1	value2
01/08	0.25 * 0.33 + 0.4 * 0.45	0.25 * 0.25 + 0.45 * 0.7
02/08	0.33	0.25
03/08	0	0
04/08	0.7 * 0.4	0.7 * 0.7

How can I achieve this?

Answer 1

Use query(), like this:

=arrayformula( 
  query( 
    { 
      B!A1, A!B1:C1; 
      B!A2:A, 
      B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) ) 
    }, 
    "select Col1, sum(Col2), sum(Col3) 
     where Col1 is not null 
     group by Col1", 
    1 
  ) 
)

This formula only shows the dates where data actually exists. To show all dates between the earliest and latest date and insert superfluous blank data in those rows, use this:

=arrayformula( 
  iferror( 
    vlookup( 
      sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ), 
      query( 
        { 
          B!A2:A, 
          B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) ) 
        }, 
        "select Col1, sum(Col2), sum(Col3) 
         where Col1 is not null 
         group by Col1", 
        0 
      ), 
      { 1, 2, 3 }, 
      false 
    ), 
    iferror( 
      sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ) / { 1, 0, 0 } 
    ) 
  ) 
)