Creating Buckets

This post will explain how to create user defined buckets. For example: a database contains a table with data of people. The

table contains an age field. The thing we want to do is divide all ages in the following predefined groups:


0 – 5 years
6 – 10 years
10 – 18 years
18 – 30 years
30 – 65 years
65 and older

(For example: the 26 year old William will be part of the group 18 – 30 years)

Approach #1:
First of all we need to create the predefined groups in the script:

Age:
LOAD * INLINE
[ AgeNR, AgeGroup
1, 0 -5 years
2, 6 - 10 years
3, 10 – 18 years
4, 18 – 30 years
5, 30 – 65 years
6, 65 and older
];

Next thing: give the age of a person the right AgeNR. Therefore the if statement is needed:

People:
LOAD Name,
Place,
Age,
if(Age <= 5,1,
(if( Age <= 10 , 2,
(if( Age <= 18, 3,
(if( Age <= 30, 4,
(if( Age <= 65, 5,
6))))))))) as AgeNR
FROM bucket.xls (biff, embedded labels, table is Blad1$);

When reloading, the 26 year old William will be part of group 4, 18 – 30 years. If age is < or = 5 then group 1. 26 is bigger

then 5 so we will continue. If age is < or = 10 then group 2. Again, 26 is bigger. If age is < or = 18 then group 3. 26 is

still bigger. If age < = 30 then group 4. 26 is smaller then 30 so it will be placed in group 4.

The next steps will not be checked for William but when a person is older then 30, these steps will look if the age is < or =

65, if true it will be placed in group 5, else the group will be 6.


Approach #2:
In this second approach we can remove the INLINE table and the group can be included in the script itself.

People:
LOAD Name,
Place,
Age,
if(Age <= 5,'0 -5 years',
(if( Age <= 10 , '6 - 10 years',
(if( Age <= 18, '10 – 18 years',
(if( Age <= 30, '18 – 30 years',
(if( Age <= 65, '30 – 65 years','65 and older'))))))))) as AgeGroup
FROM bucket.xls (biff, embedded labels, table is Blad1$);

In the above tow scripts buckets are generated statically. If you want to add more groups then changes required in the script and the script should be executed.

In Qlikview it is also possible to create these buckets dynamically using the function class. The ranges can be controlled by the end users.

Procedures to create dynamic bucket:

1. Load data into QlikView (in our example Name, Place, Age)
2. Create a variable for controlling the range.
3. Create a Inputbox object and link with the variable.
4. Create a chart, add calculated dimension using the function class, then add the expressions.

Thatz all, now user can change the range as per their requirement.