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.

Script to load all the tables from the active ODBC connection

ODBC CONNECT TO [datasource name];

Test:
SQLTables;

Test1:
Load * ,'' as dummy
Resident Test where TABLE_TYPE='TABLE';

Drop table Test;
Drop Field dummy;

Let tableName='';
FOR i = 0 to NoOfRows('Test1')-1
LET tableName = tableName & chr(39) & peek('TABLE_NAME', i, 'Test1') & chr(39);

If $(i)< NoOfRows('Test1')-1 Then
Let tableName = tableName & ',';
End If
Next

For each table in $(tableName)
[$(table)]:
Load *;
Sql Select * from [$(table)];
Next

How to Enable or Disable Chart Expressions using Macro

These macros can be used to enable/disable any chart expressions using macro.


sub Disable

set chart = ActiveDocument.GetSheetObject("CH01")
set cp = chart.GetProperties
set expr = cp.Expressions.Item(1).Item(0).Data.ExpressionData
expr.Enable = false 'disable Second expression
chart.SetProperties cp
end sub

sub Enable

set chart = ActiveDocument.GetSheetObject("CH01")
set cp = chart.GetProperties
set expr = cp.Expressions.Item(1).Item(0).Data.ExpressionData
expr.Enable = True 'enable Second expression
chart.SetProperties cp
end sub

Removing preceding Zeros

Situation:
The table field contains non numeric data preceded by zero like the one below,

FieldName
0000000234
0000123456
0000000034
0000000056

When we load this data into qlikview, it can not be converted into numeric, you will get the result like the same. but if you want to convert this non numeric data into numeric then it would be a challenge.

your general script would look like this:

Load
FieldName
From Data Source;

now, to convert this field values into numeric you can use the following functions.

Load
mid(FieldName,findoneof(FieldName, '123456789')) as FieldName
From Data Source;

This script will convert the non numeric data into numeric and also removes the preceding zeros.