topic Re: Calculate Rangesum till 20 and create sets in QlikView

Calculate Rangesum till 20 and create sets

markgraham123 — Sat, 16 Nov 2024 02:31:58 GMT

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

Re: Calculate Rangesum till 20 and create sets

sunny_talwar — Mon, 09 Sep 2019 14:28:10 GMT

Do this in the script or front end?

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Mon, 09 Sep 2019 15:06:36 GMT

@sunny_talwar

I wanted to do this in Script please.

Re: Calculate Rangesum till 20 and create sets

sunny_talwar — Mon, 09 Sep 2019 16:10:52 GMT

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;

Re: Calculate Rangesum till 20 and create sets

Brett_Bleess — Thu, 19 Sep 2019 17:29:13 GMT

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

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Mon, 23 Sep 2019 13:57:12 GMT

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?

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Mon, 23 Sep 2019 13:58:02 GMT

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

Thanks!

Re: Calculate Rangesum till 20 and create sets

sunny_talwar — Wed, 25 Sep 2019 22:23:28 GMT

What would the output look like for this new condition?

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Tue, 08 Oct 2019 18:04:13 GMT

Only creates buckets between 10 and 20.

Re: Calculate Rangesum till 20 and create sets

sunny_talwar — Tue, 08 Oct 2019 18:19:51 GMT

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

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Tue, 08 Oct 2019 20:21:10 GMT

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

Re: Calculate Rangesum till 20 and create sets

sunny_talwar — Wed, 09 Oct 2019 12:20:27 GMT

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;

Re: Calculate Rangesum till 20 and create sets

markgraham123 — Thu, 10 Oct 2019 12:18:01 GMT

As always, you are the best!

Thank you Sir!