Create table using 2 tables and a third with multipliers

[JotaEfe]

Hello!

I hope I can explain what im trying to attemp

I have 3 tables:

Table 1& Table 2 have the following structure

DATE | Name | Value

(date)| ID 1  | Value 1

I have many ID's with a vuale for each date

Both tables have the same ammount of dates but different IDs and values

I want to mix both tables using a third table wich is like this:

ID Table 1 | ID Table 2 | factor

a               |  b              |0.2

a               |  c               |.4

And so on 

I want to create a new table that prints the following column:

Date    |   ID  Table 1 |   Value

Where value = value(ID1 Table 2) * factor(Corresponding to that id) + value(ID2 Table 2) * factor .....

I hope I was clear enough if not i can bring an example

Thanks in advance

[ryan_mayu]

@JotaEfe 

pls provide the sample data and expected output.

[JotaEfe]

These are the 3 tables

DATE	ID	Product
1/1/2021	A	123
2/1/2021	A	121
3/1/2021	A	128
1/1/2021	B	243
2/1/2021	B	233
3/1/2021	B	242
1/1/2021	C	565
2/1/2021	C	566
3/1/2021	C	556

DATE	ID2	Product2
1/1/2021	J	5
2/1/2021	J	6
3/1/2021	J	5
1/1/2021	Q	3
2/1/2021	Q	2
3/1/2021	Q	3
1/1/2021	M	1
2/1/2021	M	1
3/1/2021	M	1.1

ID1	ID2	f
A	J	0.5
A	Q	0.3
B	J	0.5
B	Q	0.3
B	M	0.5
C	M	0.5

This is the desired output: 

DATE	ID	Product	Asoc. Prod
1/1/2021	A	123	3.4
2/1/2021	A	121	3.6
3/1/2021	A	128	3.4
1/1/2021	B	243	3.9
2/1/2021	B	233	4.1
3/1/2021	B	242	3.95
1/1/2021	C	565	0.5
2/1/2021	C	566	0.5
3/1/2021	C	556	0.55

For example in the first row Asoc prod = 5 * 0.3 + 3 *0.3

[ryan_mayu]

@JotaEfe 

still not clear about the logic

the first row A 123,  2021/1/1.

in the third table ,we can find J and Q which is connected to A. which is 0.5 and 0.3

should the formula be 5*0.5+3*0.3?

[JotaEfe]

Yes, it was a typo sorry

[ryan_mayu · Accepted solution]

@JotaEfe 

you can do this in PQ

pls see the attachment below

[JotaEfe]

Thanks! didnt know you could do it with PQ

[ryan_mayu]

you are welcome