Compare Min/Max for Bucket

[Anonymous]

Hi

We have requirement for comparing a aggregated value on front end compare it with Min and Max and find the bucket(this bucket would be the Dimension for a chart)

Bucket Table would be

Row Num	Min	Max	Bucket
1	0	5	0-5
2	6	10	6-10
3	11	20	11-20

And Value aggregated on front end would be compared with min and max and Bucket should be selected e.g. value is 3 than 0-5 bucket would be selected.

This Bucket would be used as dimension in a chart.

Please advise how this can be implemented.

Regards,

ASP

[Anonymous]

Thanks Andrew, but i dont want to hard-code anything as Band can change with different clients and this comes from Configurable table mentioned in my original Post.

[antoniotiman]

Try this (Dyanamic Bucket Table)

LOAD Concat('Count([Patient ID]) >='&Min,',',-Min) as Min,
Concat(Chr(39)&Bucket&Chr(39),',',-Min) as Bucket;
LOAD [Row Num] as Row,
Min,
Max,
Bucket
FROM
"https://community.qlik.com/message/1327078"
(html, codepage is 1252, embedded labels, table is @1);
LET vMin = Peek('Min');
LET vBucket = Peek('Bucket');
Temp:
Load * Inline [
Row Num,Provider ID,Patient ID
1,AA,11
2,AA,12
3,AA,13
4,BB,21
5,CC,1
6,CC,2
7,CC,3
8,CC,4
9,CC,5
10,CC,6
11,CC,7
12,CC,8
13,CC,9
14,CC,10
15,CC,21
16,CC,22
17,CC,23
25,C2,31
26,C2,32
27,C2,33
28,C2,34
29,C2,35
30,C2,36
31,C2,37
32,C2,38
35,CC,1
36,CC,2
37,CC,3
38,CC,4
39,CC,5
310,CC2,61
311,CC2,71
312,CC2,81
313,CC2,91
314,CC2,101
315,CC2,211
316,CC2,221
317,CC2,231
325,CC2,311
326,CC2,321
327,CC2,331
328,CC2,341
329,CC2,351
330,CC2,361
331,CC2,371
332,CC2,381
426,CC2,421
427,CC2,431
428,CC2,441
429,CC2,451
430,CC2,461
431,CC2,471
432,CC2,481
];
Left Join
LOAD [Provider ID],Count([Patient ID]) as CountP,
//Pick(Match(-1,Count([Patient ID]) > 11,Count([Patient ID]) > 6,Count([Patient ID]) > 0),'11-30','6-11','0-5') as Class
Pick(Match(-1,$(vMin)),$(vBucket)) as Class
Resident Temp
Group By [Provider ID];

Regards,

Antonio