topic Re: How to Select a Range of Numeric Values? in QlikView

How to Select a Range of Numeric Values?

Thu, 24 Jul 2014 12:04:15 GMT

Hi!

I have a field named "ID" and upon clicking onto a text box would like to select a range of IDs via Actions but I don't know what expression to use.

Let's assume I would like to select all IDs from 12345678 to 12387654.

What would the search string have to be?

Of course an alternative solution is welcome, too - I mean, somehow this should be possible.

Kind regards

Raffael

Re: How to Select a Range of Numeric Values?

MK_QSL — Thu, 24 Jul 2014 13:03:42 GMT

In the text box action...

Select in Field action

Field = ID

Search String

='('&concat(distinct {<ID = {">=12345678<=12387654"}>} ID, '|')&')'

Re: How to Select a Range of Numeric Values?

gandalfgray — Thu, 24 Jul 2014 13:06:24 GMT

In the search string box enter:

>=12345678 <=12387654

Re: How to Select a Range of Numeric Values?

m_woolf — Thu, 24 Jul 2014 13:06:28 GMT

Try:

>=12345678<=12387654

Re: How to Select a Range of Numeric Values?

Thu, 24 Jul 2014 13:19:47 GMT

Luckily there is a somewhat easier solution. Nonetheless I would highly appreciate if you could dissect the expression for me. I would like to understand what it is doing and what purpose the various sub-expressions serve.

Kind regards

Raffael

Re: How to Select a Range of Numeric Values?

MK_QSL — Thu, 24 Jul 2014 14:36:30 GMT

Consider that you have ID

1,2,3,4,5,6,7,8,9,10…. Upto 1000

If you want to select ID 5 and 6, you have to write (5|6)

If you want to select ID 5 to 9, you have to write (5|6|7|8|9) , But this is not a good way as you sometimes have to select from ID 100 to ID 150…

So another way is to write an expression which will give you the result (100|101|……|150)

This can be achieved by

CONCAT(YourExpression to get 100 to 150, ‘|’)

CONCAT (DISTINCT ID,’|’) will give you all ID but we need to filter between 100 to 150,

So using a set analysis..

CONCAT({<ID = {“>=100<=150”}>}DISTINCT ID)

Will give you desired result…

This we are now enclosing in ()

‘(‘*CONCAT({<ID = {“>=100<=150”}>}DISTINCT ID)&’)’

Hope this will make clear…

Regards,

Manish Kachhia

Re: Re: How to Select a Range of Numeric Values?

gandalfgray — Fri, 25 Jul 2014 06:41:15 GMT

There are situations where you may need to use the technique explained by Manish, but this is not one of them.

It is not true that you have to write (100|101|……|150) to select all ID:s from 100 to 150,

a simpler way is to write: >=100 <=150

or: >99 <151

On a side note, what result do you expect when you enter a search expression like this: >=100 <=80 ?

Maybe you expect no resulting matches since an ID can't be larger than or equal to 100 and at the same time be smaller than or equal to 80?

But that is not what you get.

What you get is the result of the OR:ed expression, that is records with ID >=100 or ID <=80

Note the "gap" below: