Calculate Rangesum till 20 and create sets

[markgraham123]

Hi All,

I'm trying to create sets of data of weight 20. 

1. I want to sort data by hour and Zone and create sets with in each hour and Zone. 

2. Create Buckets with weight 20 or close to 20 in every hour and Zone.

Please see attached QV. Any help is highly appreciated.

Here is my sample data with 'Bucket' as output:

Time,	Product,	Zone,	Weight	Bucket
7,	P1,	1,	2	1
7,	P2,	1,	3
7,	P3,	1,	5
7,	P4,	1,	1
7,	P5,	2,	16	2
7,	P6,	2,	8	3
7,	P7,	2,	12
7,	P8,	2,	15	5
7,	P9,	2,	17	6
7,	P10,	2,	19	7
7,	P11,	2,	12	8
7,	P12,	2,	11	9
7,	P13,	2,	8

[markgraham123]

Sorry Sunny.

Here is my desired output. (I just updated some values to explain with an example). 

If the sum does not add up to 10 (for one hour in one zone), it gets a value 'NA' else it follows the logic you provided above.

Here Hour 7 and Zone 1 - weight doesn't add up to 10. So, all those records get a value 'NA'

Time,	Product,	Zone,	Weight	Bucket
7,	P1,	1,	2	Cannot bucket as the weight does not sum up to a minimum of 10  - 'NA'
7,	P2,	1,	2
7,	P3,	1,	3
7,	P4,	1,	1
7,	P5,	2,	16	2
7,	P6,	2,	8	3
7,	P7,	2,	12
7,	P8,	2,	15	5
7,	P9,	2,	17	6
7,	P10,	2,	19	7
7,	P11,	2,	12	8
7,	P12,	2,	11	9
7,	P13,	2,	8

[sunny_talwar · Accepted solution]

Not so straight forward for me to understand... but see if this is what you want

T1:
LOAD * INLINE [
    Time, Product, Zone, Weight
    7, P1, 1, 2
    7, P2, 1, 2
    7, P3, 1, 3
    7, P4, 1, 1
    7, P5, 2, 16
    7, P6, 2, 8
    7, P7, 2, 12
    7, P8, 2, 15
    7, P9, 2, 17
    7, P10, 2, 19
    7, P11, 2, 12
    7, P12, 2, 11
    7, P13, 2, 8
    7, P14, 3, 12
    7, P15, 3, 45
    7, P16, 3, 67
    7, P17, 3, 9
    7, P18, 3, 12
    7, P19, 3, 32
    7, P20, 3, 8
    8, P21, 1, 12
    8, P22, 1, 45
    8, P23, 1, 67
    8, P24, 1, 9
    8, P25, 2, 12
    8, P26, 2, 32
    8, P27, 2, 8
    8, P28, 2, 12
    8, P29, 2, 45
    8, P30, 2, 67
    8, P31, 2, 9
    8, P32, 2, 12
    8, P33, 2, 32
    8, P34, 2, 8
];

T2:
LOAD *,
	 If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, RangeSum(Weight, Peek('Cum_Weight')), Weight) as Cum_Weight,
	 If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, Peek('Bucket'), RangeSum(1, Peek('Bucket'))) as Bucket
Resident T1
Order By Time, Zone, Product;

DROP Table T1;

Left Join (T2)
LOAD Bucket,
	 1 as Flag
Where Max_Cum_Weight >= 10 and Max_Cum_Weight <= 20;
LOAD Bucket,
	 Max(Cum_Weight) as Max_Cum_Weight
Resident T2
Group By Bucket;

T3:
LOAD Time,
	 Product,
	 Zone,
	 Weight,
	 Cum_Weight,
	 If(Flag = 1, Bucket) as Bucket
Resident T2;

DROP Table T2;

[markgraham123]

As always, you are the best!

Thank you Sir!