Following on from this question:
Recursive Lambda Functions for Cartesian Products in Google Sheets
How would I add columns in a generative array which looks up values from the resulting array from a seperate data source?
Refer to this google sheet for the below data, logic and formulas
Assume we have the below table as the result (a cartesian product) of an array formula (see "Code Sample" here)
| Product | Country | Date |
|---|---|---|
| ABC001 | UK | 9/4/2023 |
| ABC001 | UK | 9/11/2023 |
| ABC001 | UK | 9/18/2023 |
| ABC001 | UK | 9/25/2023 |
| ABC001 | US | 9/4/2023 |
| ABC001 | US | 9/11/2023 |
| ABC001 | US | 9/18/2023 |
| ABC001 | US | 9/25/2023 |
| ABC001 | AU | 9/4/2023 |
| ABC001 | AU | 9/11/2023 |
| ABC001 | AU | 9/18/2023 |
| ABC001 | AU | 9/25/2023 |
Now, the objective is to add 2 additional Columns (Sales and Receipts) to the above array which looks up values from 2 other tables:
Sales Table
| Product | Country | Date | Sales |
|---|---|---|---|
| ABC001 | UK | 9/4/2023 | $100.00 |
| ABC001 | UK | 9/25/2023 | $200.00 |
| ABC001 | US | 9/11/2023 | $350.00 |
| ABC001 | AU | 9/18/2023 | $120.00 |
| ABC001 | AU | 9/25/2023 | $80.00 |
Receipts Table
| Product | Country | Date | Receipts |
|---|---|---|---|
| ABC001 | UK | 9/11/2023 | 12 |
| ABC001 | UK | 9/18/2023 | 5 |
| ABC001 | US | 9/4/2023 | 7 |
| ABC001 | AU | 9/18/2023 | 15 |
Final Result
After joining these tables together the result will be this:
| Product | Country | Date | Sales | Receipts |
|---|---|---|---|---|
| ABC001 | UK | 9/4/2023 | $100 | |
| ABC001 | UK | 9/11/2023 | 12 | |
| ABC001 | UK | 9/18/2023 | 5 | |
| ABC001 | UK | 9/25/2023 | $200 | |
| ABC001 | US | 9/4/2023 | 7 | |
| ABC001 | US | 9/11/2023 | $350 | |
| ABC001 | US | 9/18/2023 | ||
| ABC001 | US | 9/25/2023 | ||
| ABC001 | AU | 9/4/2023 | ||
| ABC001 | AU | 9/11/2023 | ||
| ABC001 | AU | 9/18/2023 | $120 | 15 |
| ABC001 | AU | 9/25/2023 | $80 |
I am looking for something that can be contained and included dynamically in the original array formula that generates the Cartesian Product. I dont want a helper style column, ie. where we need a formula in the adjacent columns that does a SUMIFS / Vlookup / Index-Match formula that needs to be dragged down to calculate the lookup on a static formula. I am hoping to understand if there is some kind of design pattern I could learn to dynamically create additional columns in generative arrays that looks up data based on the rows in the array itself.
Stated another way, if the below is the code that generates the Cartesian Product, how can this be tweaked to dynamically add 2 columns that looks up data based on the values of the rows in the Cartesian Product itself:
=let(
table, A2:C,
blank, iferror(1/0),
first_, lambda(array, tocol(choosecols(array, 1), true)),
rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))),
wrap_, lambda(array, wrapCount, wraprows(tocol(array, 1), wrapCount)),
cartesian_, lambda(a, b, wrap_(
byrow(a, lambda(row,
reduce(blank, sequence(rows(b)), lambda(acc, i,
{ acc, row, chooserows(b, i) }
) )
) ),
columns(a) + columns(b)
) ),
iterate_, lambda(
self, a, b, if(iserror(b), a,
self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1))
)
),
iterate_(iterate_, first_(table), rest_(1))
)
You could try something like this:
=let(
table, A4:C,
blank, iferror(1/0),
first_, lambda(array, tocol(choosecols(array, 1), true)),
rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))),
wrap_, lambda(array, wrapCount, wraprows(tocol(array, 1), wrapCount)),
cartesian_, lambda(a, b, wrap_(
byrow(a, lambda(row,
reduce(blank, sequence(rows(b)), lambda(acc, i,
{ acc, row, chooserows(b, i) }
) )
) ),
columns(a) + columns(b)
) ),
iterate_, lambda(
self, a, b, if(iserror(b), a,
self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1))
)
),
ftable, iterate_(iterate_, first_(table), rest_(1)),
join_, lambda(array, byrow(array, lambda(r, join(,r)))),
jtable, join_(ftable),
{ftable,arrayformula({vlookup(jtable,{join_(K4:M12),N4:N12},2,),vlookup(jtable,{join_(P4:R12),S4:S12},2,)})}
)