topic Re: Add two dimensions in one in Water Cooler

Add two dimensions in one

PEyzaguirre — Mon, 17 Dec 2018 18:41:42 GMT

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

300

2.167.167

500

1.334.083

150

900

4.594.428

1.035.606

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.

Re: Add two dimensions in one

dplr-rn — Mon, 17 Dec 2018 20:11:37 GMT

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

Re: Add two dimensions in one

PEyzaguirre — Mon, 17 Dec 2018 20:46:48 GMT

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

Re: Add two dimensions in one

dplr-rn — Mon, 17 Dec 2018 20:50:55 GMT

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

Re: Add two dimensions in one

PEyzaguirre — Mon, 17 Dec 2018 20:58:51 GMT

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