Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

Calculate Rangesum till 20 and create sets

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,WeightBucket
7,P1,1,21
7,P2,1,3
7,P3,1,5
7,P4,1,1
7,P5,2,162
7,P6,2,83
7,P7,2,12
7,P8,2,155
7,P9,2,176
7,P10,2,197
7,P11,2,128
7,P12,2,119
7,P13,2,8
Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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

12 Replies
sunny_talwar

Do this in the script or front end?

markgraham123
Specialist
Specialist
Author

@sunny_talwar 

I wanted to do this in Script please.

sunny_talwar

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
Support (Former)
Support (Former)

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

Thanks!

sunny_talwar

What would the output look like for this new condition?

markgraham123
Specialist
Specialist
Author

Only creates buckets between 10 and 20.

sunny_talwar

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