topic Custom Filter(unusual range) in QlikView

Custom Filter(unusual range)

Fri, 06 Jun 2014 19:15:28 GMT

I need to transform a field into a filter whight some custom range. So i need 3 different steps in one range filter, is it possible?

premius:

load

if(premius=0 and premius=100,'100-500 $',

if(premius=100.1 and premius=500,'100.1-500 $',

if(premius= 500.1 and premius = 1000, '500.1-1000 $',

if(premius= 1000.1 and premius = 2000, '1000.1-2000 $',

.......

if(premius= 29000.1 and premius = 30000, '29000.1-30000 $',

if(premius= 30000.1 and premius = 30000, '30000.1-40000 $',

......

if(premius= 100000.1 and premius = 150000, '100000.1-150000 $',

if(premius= 150000.1 and premius = 200000, '150000.1-200000 $',

'more 250000 $', premius&' $'))))))) as PremiusFilter

from premius.qvd

Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:17:14 GMT

I think you want to use if(premiums >= x and premiums <=y, 'X-Y $'....

Using and will never work because premiums cant be two numbers at the same time.

Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:23:31 GMT

Premius x to premius by, something like it. The problem is the range have 4 different steps, so i already understand how can i make something like it but if the range do not change. But what should i do in this situation.

Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:26:54 GMT

hi try this hope this helps you

in script write this

LOAD

premius,

even(premius) as x,

odd(premius)as y,

from table;

then try this

if(x<>null() and y<>null(), 'x-y $') as as PremiusFilter

Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:28:04 GMT

When you say four different steps do you mean:

for 100-500, steps: 100-200, 200-300, 300-400, 400-500?

Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:32:33 GMT

Yes, somefing like it

from 0 to 1000 steps by 100

from 1000.1 to 30000 steps by 1000

from 30000.1 to 100000 steps by 10000

from 100000.1 to 200000 steps by 50000

and more 250000

Re: Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:51:57 GMT

I made a quick excel file and qvw for you. This might help you.

Re: Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:59:11 GMT

Thank you i'll try to think of something but the problem is i have only x, so i need pre load and make y somehow.

Re: Re: Custom Filter(unusual range)

Fri, 06 Jun 2014 19:59:39 GMT

It is interestion option, but i am afraid i would not work for me

Re: Custom Filter(unusual range)

MarcoWedel — Sun, 08 Jun 2014 00:11:33 GMT

Hi Ivan,

one solution could be also:

tabPremius:
LOAD exp(log(50)+Rand()*log(300000/50)) as premius
AutoGenerate 100;

tabPremiusClass:
LOAD *,
     AutoNumberHash128(RangeMin, RangeMax) as %ClassID;
LOAD RangeMin,
     Alt(Peek(RangeMin), Dual('∞', 100000000000000)) as RangeMax
Inline [
RangeMin
250000
200000
150000
100000
90000
80000
70000
60000
50000
40000
30000
29000
28000
27000
26000
25000
24000
23000
22000
21000
20000
19000
18000
17000
16000
15000
14000
13000
12000
11000
10000
9000
8000
7000
6000
5000
4000
3000
2000
1000
900
800
700
600
500
400
300
200
100
0
];

tabIntMat:
IntervalMatch (premius)
LOAD RangeMin, RangeMax
Resident tabPremiusClass;

Left Join (tabPremius)
LOAD premius,
     Dual(RangeMin&'-'&RangeMax, RangeMin) as PremiusFilter,
     AutoNumberHash128(RangeMin, RangeMax) as %ClassID
Resident tabIntMat;

DROP Table tabIntMat;

hope this helps

regards

Marco

Re: Custom Filter(unusual range)

Sun, 08 Jun 2014 06:18:48 GMT

Thank you it is what i need.

Re: Custom Filter(unusual range)

MarcoWedel — Sun, 08 Jun 2014 07:48:42 GMT

You're welcome.

Regards

Marco