Hi There
I was wondering the best way to achieve the following. I have the following two tables and want to create a third table which breaks out the total values in Table 1 using the percentage splits listed in Table 2 to calculate the Amount column in the output table.
There could be various descriptions and % splits listed per Item_Ref in Table 2
Hopefully the below examples shows what I am trying to achieve.
Thanks in advance for any assistance!
<tbody>
</tbody>
<tbody>
</tbody>
Output Table
<tbody>
</tbody>
I was wondering the best way to achieve the following. I have the following two tables and want to create a third table which breaks out the total values in Table 1 using the percentage splits listed in Table 2 to calculate the Amount column in the output table.
There could be various descriptions and % splits listed per Item_Ref in Table 2
Hopefully the below examples shows what I am trying to achieve.
Thanks in advance for any assistance!
Table 1 | |
Item_Ref | Total_Value |
1001 | 500 |
1002 | 200 |
1003 | 250 |
<tbody>
</tbody>
Table 2 | ||
Item_Ref | Description | Percentage |
1001 | Postage | 0.1 |
1001 | Fee | 0.03 |
1001 | Item | 0.87 |
1002 | Postage | 0.1 |
1002 | Fee | 0.02 |
1002 | Item | 0.88 |
1003 | Postage | 0.2 |
1003 | Fee | 0.1 |
1003 | Tax | 0.2 |
1003 | Item | 0.5 |
<tbody>
</tbody>
Output Table
Item_Ref | Description | Total_Value | Percentage | Amount |
1001 | Postage | 500 | 0.1 | 50 |
1001 | Fee | 500 | 0.03 | 15 |
1001 | Item | 500 | 0.87 | 435 |
1002 | Postage | 200 | 0.1 | 20 |
1002 | Fee | 200 | 0.02 | 4 |
1002 | Item | 200 | 0.88 | 176 |
1003 | Postage | 250 | 0.2 | 50 |
1003 | Fee | 250 | 0.1 | 25 |
1003 | Tax | 250 | 0.2 | 50 |
1003 | Item | 250 | 0.5 | 125 |
<tbody>
</tbody>