Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
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 |
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;