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

Custom Filter(unusual range)

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

1 Solution

Accepted Solutions
MarcoWedel

Hi Ivan,

one solution could be also:

QlikCommunity_Thread_121191_Pic1.JPG.jpg

QlikCommunity_Thread_121191_Pic2.JPG.jpg

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

View solution in original post

11 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

When you say four different steps do you mean:

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

MarcoWedel

Hi Ivan,

one solution could be also:

QlikCommunity_Thread_121191_Pic1.JPG.jpg

QlikCommunity_Thread_121191_Pic2.JPG.jpg

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