Add two dimensions in one

[PEyzaguirre]

Dear Ones, I turn to you again for help. I have a sales table, associated with a code and a referral. For example the code 20 was referred by code 300:

Codigo	Ref	Sum(Venta)
		18.272.049
20	300	2.167.167
5	500	1.334.083
150	900	4.594.428
1		1.035.606
9		1.174.284
500		1.977.784
900		2.971.565
300		3.017.132

So if I want to see the contribution of code 300 I should consider besides the sale of code 20.

Codigo	Sum(Monto)	Monto Ref	A+B
	18.272.049
20	2.167.167		2.167.167
5	1.334.083		1.334.083
150	4.594.428		4.594.428
1	1.035.606		1.035.606
9	1.174.284		1.174.284
500	1.977.784	1.334.083	3.311.867
900	2.971.565	4.594.428	7.565.993
300	3.017.132	2.167.167	5.184.299

I hope you can help me.

[dplr-rn]

Hi

Assuming that the Sum(Venta) is a number but in a different format

Master:
LOAD Codigo, 
     Ref, 
     [Sum(Venta)]
FROM
[.\Comm1521800.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left join (Master)
Load
	Ref as Codigo, 
    [Sum(Venta)] as [Monto Ref]
resident Master
where IsNull(Ref)=0;

MasterFinal:
Load *,
	[Sum(Venta)]+[Monto Ref]	as [A+B]
resident Master;

drop table Master;

Output

Codigo	Ref	Sum(Venta)	Monto Ref	A+B
1		1035.606
5	500	1334.083
9		1174.284
20	300	2167.167
150	900	4594.428
300		3017.132	2167.167	5184.299
500		1977.784	1334.083	3311.867
900		2971.565	4594.428	7565.993
		18272.049

[PEyzaguirre]

Thanks for the reply, but the magic must be in the object and not in the script

[dplr-rn]

Why?
There may be a way of doing it on the object in some complicated way. But that most likely will degrade performance.

[PEyzaguirre]

That is correct, but it has to be in the object since it must be sensitive to the available selections