topic Re: Selecting a range over two fields? in QlikView

Selecting a range over two fields?

Wed, 11 May 2011 19:19:32 GMT

Hi,

I have two fields - LowFreq and HighFreq. I want to be able to select all the records that are in a certain range, let's call them l_freq and u_freq. i can set up a select string that will search over the LowFreq, but I need to be able to select the range. Meaning - it should select record that has a LowFreq that is lower than l_freq, but has a HighFreq that is higher than l_freq. The table below gives a better explanation on items that need to be selected.

if the frequency range that I want to select is 7-25, then the highlighted records should be selected.

Record ID	LowFreq	HighFreq
1	10	20
2	2	5
3	2	12
4	17	32
5	30	35
6	2	50

Is there a way to do that?

Thanks in advance!

Therese

Selecting a range over two fields?

Thu, 12 May 2011 08:23:06 GMT

Hello Therese,

I got what you want to realize. But I don't know right now, where you want to implement it. In or as a search mask (listbox or button) or use it with variables (are l_freq and u_freq variables or input fields?). Or just to highlight the recordID in a straight table in your post above?

Regards, Roland

Selecting a range over two fields?

Thu, 12 May 2011 13:29:53 GMT

Hi Roland,

Preferably I would like to implement this in a button, most likely using a macro because I don't think it would be doable in series of actions.

Currently, l_freq and u_freq are taken from another table (Band_num) which is not associated with the table that includes LowFreq and HighFreq (Allocs). When the user selects a band or two in Band_num, then hits the button, I want it to select the records in Allocs that fall within the range specified by the band(s) that are selected in Band_num.

Thanks in advance!

Therese

Re: Selecting a range over two fields?

erichshiino — Thu, 12 May 2011 14:44:19 GMT

If you want to hightlight, it's possible to do it without macro.

If you need it with macro, maybe we got another step

Hope it helps,

Erich

Re: Selecting a range over two fields?

Thu, 12 May 2011 14:46:30 GMT

Erich,

I hate to be a pain, but I only have the personal edition right now. We are evaluating to see if Qlikview will fit the bill. Is there another way you can show me what you have in the document?

Thanks.

Re: Selecting a range over two fields?

erichshiino — Thu, 12 May 2011 14:51:26 GMT

Create this on script:

LOAD * INLINE [

REG_ID, L_FREQ, U_FREQ

1, 5, 10

2, 8, 12

3, 1, 5

4, 20, 30

5, 14, 30

];

Created two variables to hold the min Limit and max Limit (vMin & vMax)

used this expression to identify the registers:

count( {$-<L_FREQ={'>=$(vMax)'}>-<U_FREQ={'<=$(vMin)'}>}REG_ID)

used the following expression to highlight in green the lines according to the limits (on a straight table)

if( count( {$-<L_FREQ={'>=$(vMax)'}>-<U_FREQ={'<=$(vMin)'}>}REG_ID)>0, lightgreen())

Re: Selecting a range over two fields?

Thu, 12 May 2011 16:34:52 GMT

Erich, I am confused as to where exactly to place those equations. Further, if I understand you correctly, this will only highlight the records in the straight table. Is this correct? If so, this isn't sufficient, as I need to apply the limits as part of the selection of records.

Additionally, This only works for one variable range... could this be extrapolated to additional ranges as well?

Thanks,

Therese

Re: Selecting a range over two fields?

Tue, 17 May 2011 20:07:23 GMT

Anyone else have suggestions? Thanks!

Re: Selecting a range over two fields?

Thu, 18 Aug 2011 17:56:07 GMT

Just to close this out, I ended up doing a search on the first column, storing the ids for the records that fit. Then checking the second criteria, store those ids. Then go in and select the stored ids. It was a bit of a work around, but it does the trick.