Calculate Distinct value of Rangesum

[markgraham123]

Hi All,

I want to calculate Rangesum for below column 'Weight' taking in the distinct value of order.

Code i'm using gives me Range sum by counting same value multiple times:

If(Zone= Previous(Zone) and Time = Previous(Time), RangeSum(Cum_Wt, Peek('Cum_Wt')), Weight) as Cum_Wt

Current O/P : 280

Desired Output : 70

Here is the sample data:

Can someone please help on how can i only Rangesum Weight of an Order only once?

Time	Zone	Order	SKU	Weight
7	B	7895647	1A	10
7	B	7895647	2B	10
7	B	7895647	2A	10
7	B	7895647	2C	10
8	B	8763190	3D	20
8	B	8763190	3A	20
8	B	8763190	4B	20
8	B	8763190	5E	20
9	C	7641839	1B	25
9	C	7641839	4D	25
9	C	7641839	3D	25
9	C	7641839	1K	25
10	D	8566128	2J	15
10	D	8566128	2P	15
10	D	8566128	5T	15
10	D	8566128	8U	15

Any help is highly appreciated.

[markgraham123]

@sunny_talwar 

[sunny_talwar]

The code you showed is not going to give you a single value... it will show a value on each row... I am not sure if you want a single value or row for each value here?

[markgraham123]

Here is the desired output in the last column, Sunny...

Time	Zone	Order	SKU	Weight	Cum_Wt
7	B	7895647	1A	10	10
7	B	7895647	2B	10	10
7	B	7895647	2A	10	10
7	B	7895647	2C	10	10
8	B	8763190	3D	20	30
8	B	8763190	3A	20	30
8	B	8763190	4B	20	30
8	B	8763190	5E	20	30
9	C	7641839	1B	25	55
9	C	7641839	4D	25	55
9	C	7641839	3D	25	55
9	C	7641839	1K	25	55
10	D	8566128	2J	15	70
10	D	8566128	2P	15	70
10	D	8566128	5T	15	70
10	D	8566128	8U	15	70

[sunny_talwar · Accepted solution]

Table:
LOAD * INLINE [
    Time, Zone, Order, SKU, Weight
    7, B, 7895647, 1A, 10
    7, B, 7895647, 2B, 10
    7, B, 7895647, 2A, 10
    7, B, 7895647, 2C, 10
    8, B, 8763190, 3D, 20
    8, B, 8763190, 3A, 20
    8, B, 8763190, 4B, 20
    8, B, 8763190, 5E, 20
    9, C, 7641839, 1B, 25
    9, C, 7641839, 4D, 25
    9, C, 7641839, 3D, 25
    9, C, 7641839, 1K, 25
    10, D, 8566128, 2J, 15
    10, D, 8566128, 2P, 15
    10, D, 8566128, 5T, 15
    10, D, 8566128, 8U, 15
];

FinalTable:
LOAD *,
	 If(Time = Previous(Time) and Zone = Previous(Zone), Peek('Cum_Wt'), RangeSum(Peek('Cum_Wt'), Weight)) as Cum_Wt
Resident Table
Order By Time, Zone;

DROP Table Table;