Selecting a range over two fields?

[#1]

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

[#2]

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

[#3]

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

[erichshiino]

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

[#5]

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.

[erichshiino]

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())

[#7]

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

[#8]

Anyone else have suggestions?  Thanks!

[#9 · Accepted solution]

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.