topic Help With Custom Range in QlikView

Help With Custom Range

Wed, 14 Mar 2012 18:06:56 GMT

I want to know how to create a custom range for example

I have the following data

Tab1:Load * Inline [Id,amt
1,5
2,10
3,15
5,20
6,6
7,7
8,23
9,16
10,4
11,22
12,16
13,9
14,1
15,28];

I want to create a range between amt 0-5 (a) and 5-13 (b),and 13-20 (c), 20-30 (d)

Here is an example of what I want to create

50 -| __

40 -| __ | |

30 -| | | __ __ | |

20 -|_ | | __| | ___ | | ___| |_

(a) (b) (c) (d)

x - range

y - Count(Id) by the range

Let me know if need more info

Thanks

Re: Help With Custom Range

Wed, 14 Mar 2012 20:03:23 GMT

I am not quite sure if this is exactly what you are looking for but I'll give you a couple options just in case:

The way I solved this is in the script:

Tab1:

Load * Inline

[Id,amt

1,5

2,10

3,15

5,20

6,6

7,7

8,23

9,16

10,4

11,22

12,16

13,9

14,1

15,28];

tab2:

LOAD *,

if(amt>0 and amt <5, 'a',

if(amt>5 and amt <13, 'b',

if(amt>13 and amt < 20, 'c',

if(amt>20 and amt < 30, 'd')))) as CustomRange

Resident Tab1;

drop table Tab1;

Now the results are in tab2, keep in mind if you want 0-5 inclusive, etc... you will want:

if(amt>0 and amt <= 5, 'a',

if(amt>5 and amt <= 13, 'b',

if(amt>13 and amt <= 20, 'c',

if(amt>20 and amt <= 30, 'd')))) as CustomRange

Also if you didn't want the literals 'a', 'b', 'c', 'd' you could always replace them with 0-5, 5-13, etc... by:

if(amt>0 and amt <= 5, '0-5',

if(amt>5 and amt <= 13, '5-13',

if(amt>13 and amt <= 20, '13-20',

if(amt>20 and amt <= 30, '20-30')))) as CustomRange

I also attached anapplication for additional help if you need it.

But basically once you have the script the way you want it reload. Create a bar chart with a dimension of CustomRange and the expression =COUNT (CustomRange), and you should have something close to what you are looking for!

Hope this helps!

Re: Help With Custom Range

Wed, 14 Mar 2012 20:27:26 GMT

Add a second table :

Tab2 :

Load * inline [begin,end,range

0,5,a

6,13,b

14,20,c

21,30,d];

IntervalMatch (amt) load begin, end resident Tab2;

IntervalMatch match discrete values to intervals.

Help With Custom Range

Thu, 15 Mar 2012 19:59:45 GMT

Thanks for the help