new values (calculations) depending on hierarchy

[urazbekov]

Hello everyone, 

I met with a challenge to calculate new values depending on values of subcategory. 

In original, I have such a table

Б	В	1
1.0	А00-Т98	8900
2.0	А00-В99	1140
2.1	В16.0-В17.8	5
2.2	В18.0-В19.9	455
3.0	С00-D48	3500
3.1	D10-D36	2600

So, here we see that group 1.0 includes values of 2.0 and 3.0, and 2.0 includes 2.1 and 2.2. In the end I would like to get new values of 1.0 (what remains without 2.0 and 3.0), 2.0 (what remains without 2.1 and 2.2) and others, like in the following table: 

Б	В	1	2	comment for calculations
1.0	А00-Т98	8900	4260	8900 - 1140 - 3500
2.0	А00-В99	1140	680	1140 - 5 - 455
2.1	В16.0-В17.8	5	5	5
2.2	В18.0-В19.9	455	455	455
3.0	С00-D48	3500	900	3500 - 2600
3.1	D10-D36	2600	2600	2600

Hope to see your suggestions. 

Thank you! 

[chrismarlow]

Hi,

A bit messy, but try this;

data:
LOAD * INLINE [
Б,В,1
1,А00-Т98,8900
2,А00-В99,1140
2.1,В16.0-В17.8,5
2.2,В18.0-В19.9,455
3,С00-D48,3500
3.1,D10-D36,2600
];

left join (data)
Load
	1 as Б,
	Sum([1]) as sub_from_1
Resident data
Where Б<>1 and Floor(Б,1)=Б
Group By 1;

left join (data)
Load
	Floor(Б,1) as Б,
	Sum([1]) as sub_from_int
Resident data
Where Б<>1 and Floor(Б,1)<>Б
Group By Floor(Б,1);

data_final:
NoConcatenate
Load
	Б,
	В,
	[1],
	[1]-If(isnull(sub_from_1),0,sub_from_1)-If(isnull(sub_from_int),0,sub_from_int)as [2]
resident data;

DROP TABLE data;

Cheers,

Chris.