Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help With Custom Range

Hi

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

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

3 Replies
Not applicable
Author

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!

Not applicable
Author

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.

Not applicable
Author

Thanks for the help