Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted

Re: Custom Filter(unusual range)

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
Highlighted
Not applicable

Re: Custom Filter(unusual range)

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.

Highlighted
Not applicable

Re: Custom Filter(unusual range)

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.

Highlighted
Not applicable

Re: Custom Filter(unusual range)

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

Highlighted
Not applicable

Re: Custom Filter(unusual range)

When you say four different steps do you mean:

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

Highlighted
Not applicable

Re: Custom Filter(unusual range)

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

Highlighted
Not applicable

Re: Re: Custom Filter(unusual range)

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

Highlighted
Not applicable

Re: Re: Custom Filter(unusual range)

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.

Highlighted
Not applicable

Re: Re: Custom Filter(unusual range)

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

Highlighted

Re: Custom Filter(unusual range)

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