dataset ln_mon_ref
LNo 12_Month_A1 12_Month_A6 12_Month_Base 12_Month_A2 12_Month_A4
23 290.7546354 448.57426097 1880.3844491 1116.960121 406.90120716
24 0.1278847503 0.1573367909 0.5645658305 0.3878716946 0.1324158737
25 56.02135594 65.7289856 282.42013204 174.8432417 59.878547019
dataset sc_original
Scenario Original 10/30/30/30 33/0/33/33/0 50/0/0/50/0 50/0/25/25/0
12_Month_A1 0.1071 0 0 0 0
12_Month_A6 0.1071 0.3 0 0 0
12_Month_Base 0.4464 0.1 0.333333333 0.5 0.5
12_Month_A2 0.2683 0.3 0.333333333 0 0.25
12_Month_A4 0.0711 0.3 0.333333333 0.5 0.25
based on LNO column 12_Month_A1 column should divide and multiply original*10/30/30/30 based on scenario column
from dataset sc_original as shown below example calculation.
I need four addition column sc_30_10,sc_33_33,sc_50_50,sc_50_25 each column should give different scenrio results
as shown in Expected output
calculation example:
scenario_30_10
=290.7546354/0.1071*0+448.57426097/0.1071*0.3+1880.3844491/0.4464*0.1+1116.960121/0.2683*0.3+406.90120716/0.0711*0.3
=0.1278847503/0.1071*0+0.1573367909/0.1071*0.3+0.5645658305/0.4464*0.1+0.3878716946/0.2683*0.3+0.1324158737/0.0711*0.3
=56.02135594/0.1071*0+65.7289856/0.1071*0.3+282.42013204/0.4464*0.1+174.8432417/0.2683*0.3+59.878547019/0.0711*0.3
scenario_33_33
=290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.333333333+1116.960121/0.2683*0.333333333+406.90120716/0.0711*0.333333333
=0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.333333333+0.3878716946/0.2683*0.333333333+0.1324158737/0.0711*0.333333333
=56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.333333333+174.8432417/0.2683*0.333333333+59.878547019/0.0711*0.333333333
scenario_50_50
=290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.5+1116.960121/0.2683*0+406.90120716/0.0711*0.5
=0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.5+0.3878716946/0.2683*0+0.1324158737/0.0711*0.5
=56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.5+174.8432417/0.2683*0+59.878547019/0.0711*0.5
scenario_50_25
=290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.5+1116.960121/0.2683*0.25+406.90120716/0.0711*0.25
=0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.5+0.3878716946/0.2683*0.25+0.1324158737/0.0711*0.25
=56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.5+174.8432417/0.2683*0.25+59.878547019/0.0711*0.25
Expected output:
LNo 12_Month_A1 12_Month_A6 12_Month_Base 12_Month_A2 12_Month_A4 sc_30_10 sc_33_33 sc_50_50 sc_50_25
23 290.7546354 448.574261 1880.384449 1116.960121 406.9012072 4644 4699 4968 4578
24 0.12788475 0.157336791 0.564565831 0.387871695 0.132415874 2 2 2 1
25 56.02135594 65.7289856 282.420132 174.8432417 59.87854702 696 709 737 690
