Custom Filter(unusual range)

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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

[#5]

When you say four different steps do you mean:

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

[#6]

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

[#7]

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

[#8]

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.

[#9]

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

[MarcoWedel · Accepted solution]

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