Solved: Re: Calculate Rangesum till 20 and create sets - Qlik Community

markgraham123 · ‎2019-09-09

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

sunny_talwar · ‎2019-10-09

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;

View solution in original post

sunny_talwar · ‎2019-09-09

Do this in the script or front end?

markgraham123 · ‎2019-09-09

@sunny_talwar

I wanted to do this in Script please.

sunny_talwar · ‎2019-09-09

May be this

T1:
LOAD * INLINE [
    Time, Product, Zone, Weight
    7, P1, 1, 2
    7, P2, 1, 3
    7, P3, 1, 5
    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;

Brett_Bleess · ‎2019-09-19

Mark, did Sunny's post help you get what you needed? If so, please be sure to use the Accept as Solution button on the post that worked to give Sunny credit and let others know what worked. If you are still trying to get things to work, leave us an update on where things stand.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

markgraham123 · ‎2019-09-23

Hi Sunny,

Apologize for late reply. This is perfect!

One final request - How do i set minimum threshold limit to create a bucket.

Max is 20, but if i want to set Min as 10 for creation of bucket, can you please help?

markgraham123 · ‎2019-09-23

Hi Brett, I apologize for late reply. I'm travelling and not able to get back to it.

Thanks!

sunny_talwar · ‎2019-09-25

What would the output look like for this new condition?

markgraham123 · ‎2019-10-08

Only creates buckets between 10 and 20.

sunny_talwar · ‎2019-10-08

Not sure I follow when you say the bucket should be between 10 and 20?

Calculate Rangesum till 20 and create sets

Above() function

peek()

rangesum