Forum Discussion
Solved
Multiply the values from multiple columns with each correspond weight value with the name of the col
i want to create a new table that will be a combination of two tables.
Table 1 has 5 columns.
| player_id | position_id | kpi_1 | kpi_2 | kpi_3 |
| 001 | atacante | 0.12 | 1.2 | 1.4 |
| 002 | volante | 1 | 3 | 1.4 |
| 003 | meia | 2 | 2 | 1.4 |
| 004 | atacante | 1 | 4 | 1.4 |
| 005 | meia | 4 | 5 | 1.4 |
and the table 2 have 3 columns:
| kpi_name | position | weight |
| kpi_1 | atacante | 1.5 |
| kpi_2 | atacante | 2 |
| kpi_3 | atacante | 0 |
| kpi_1 | volante | 1 |
| kpi_2 | volante | 0 |
| kpi_3 | volante | 2 |
| kpi_1 | meia | 3 |
| kpi_2 | meia | 4 |
| kpi_3 | meia | 1 |
my new table has to be just like the first one, with the same columns but, will multiply the values of the each kpi columns with the weight numeric value in each row correspondent to the name of the kpi and the position.
Just like that:
| player_id | position_id | kpi_1 | kpi_2 | kpi_3 |
| 001 | atacante | 0.18 | 2.4 | 0 |
| 002 | volante | 1 | 0 | 2.8 |
| 003 | meia | 6 | 8 | 1.4 |
| 004 | atacante | 2.25 | 8 | 0 |
| 005 | meia | 12 | 20 | 1.4 |
how can i get what i want? how can i multiply the values from the kpi_1,kpi_2 and kpi_3 with the respectvily weigth that has the kpi_name of the column in table 1?
(have in mind that i have 100 kpi columns, thats why i want a more automated way...)
