Multiply tables together dynamically

[Casual]

I have two tables e.g.

Table1

Record ID	0 Days	7 Days	14 Days	21 Days
1	10	20	30	40
2	50	60	70	80
3	90	100	110	120

Table2

2021_03_23	2021_03_30	2021_04_06	2021_04_13
0.999	0.987	0.976	0.854

I need to to multiply each value in table 1 (excluding column Record ID) by a corresponding amount in table 2. I.e. I'd ultimately like to end up with a table that looks like the below

Record ID	2021_03_23	2021_03_30	2021_04_06	2021_04_13
1	=10* 0.999	=20*0.987	=30*0.976	=40*0.854
2	=50*0.999	=60*0.987	=70*0.976	=80*0.854
3	=90*0.999	=100*0.987	=110*0.976	=120*0.854

The number of dated columns in table 2 will always precisely match the number of value columns in table 1 (i.e. 0days, 7days etc) however the column headers in table 2 will change in every run so it would be hugely helpful if the columns could be multiplied together dynamically based on position. Though if worse comes to worst it is possible to edit the source data ahead of input so the headers match.

Apologies not to attach an attempt at a solution but after numerous attempts with 'append'ing the tables together and using a 'multi field formula' tool I haven't come close to a successful solution.

Thanks in advance for any help you can provide!

[AngelosPachis · Accepted solution]

Hi @Casual ,

What I tried in this occasion is transposing both streams so I bring them in a thin and long format. Then I parsed out the days number from both streams and used that to Join on.

Then it was just a matter of bringing the data to the original structure.

Hope that helps, let me know if that worked for you or you have any questions on the workflow.

Cheers,

Angelos

[patrick_digan]

@Casual In case you wanted to try a macro approach, I have a multi-field multi-row formula macro that would work in a case like this:

I'm currently assuming you'll always have 4 columns, but you could easily make that a formula so it's more dynamic:

[Casual]

Thank you, this worked perfectly! Will certainly be adding the transpose cross tab method for matrix multiplication to the tool box.

I slightly adjusted it so I was joining on the named days (0 days, 7 days, ... etc) instead of using the date reference just so it could be re-run for historic periods without needing to convert these into date formats using dateToday().

Thanks again!