topic Correct syntax for "between" or ''in''(as in sql) within expression? in QlikView

Correct syntax for "between" or ''in''(as in sql) within expression?

Fri, 11 May 2012 14:55:47 GMT

Hi All

I am trying to use the GetselectedCount function within an 'IF' expression. What I need is for the expression to do is to sum up a value if the GetSelectedCount is either 1,2 or 3. I know this would be a simple 'is in' or 'between' in SQL, but am not sure of the QV syntax.

I have tried

IF (GetSelectedCount([MY FIELD)]) = {1,2,3} , 'output')

but it returns no results.

To explain further using the SIMPLE syntax from a text box below how would I need to amend my syntax if I wanted to return a single line/word if 1,2 or 3 fields were selected from 'MY FIELD?

=IF (GetSelectedCount([MY FIELD)])   = 0 , 'None Selected', 
 IF (GetSelectedCount([MY FIELD)])   = 1 , 'One Selected', 
 IF (GetSelectedCount([MY FIELD)])   = 2 , 'Two Selected',
 IF (GetSelectedCount([MY FIELD)])  >= 3 , 'Three or more Selected',
  'Error'))))

Many thanks and happy FRIDAY!

Scott

Correct syntax for "between" or ''in''(as in sql) within expression?

phaneendra_kunc — Fri, 11 May 2012 15:47:43 GMT

Hi,

Try this.

sum( {<GetSelectedCount([MY FIELD)])= {'>=1)<=3'}>} value)

Regards,

Phani

Correct syntax for "between" or ''in''(as in sql) within expression?

Mon, 14 May 2012 10:25:53 GMT

HI Phani

Thanks for the reply.

However, the sum functions is not what I am after. My exact expresion would be this:

=if(GetSelectedCount([Fee Earner (WTK)]) XXX, '1 OR 2 OR 3 SELECTED', '0 or 4+ selected')

where the xxx would read 1 2 or 3.

I have tried {1,2,3} and vaiuos combinations of >=1 <=3 but nothing works as expected. Any ideas would be most apprciated.

Thx

Scott

Correct syntax for "between" or ''in''(as in sql) within expression?

Mon, 14 May 2012 10:49:22 GMT

Hi Scott,

I'm not sure about what your data looks like (perhaps some sort of set analysis is possible), but if you want to keep the syntax simple, you can always try the OR function... so:

=if(GetSelectedCount([Fee Earner (WTK)]) =1 or

GetSelectedCount([Fee Earner (WTK)]) =2 or

GetSelectedCount([Fee Earner (WTK)]) =3,

, '1 OR 2 OR 3 SELECTED', '0 or 4+ selected')

This should work for selections greater than 1.

If the user doesn't select anything, and you want them to do so first, you should add a condition to show the object like:

GetSelectedCount([Fee Earner (WTK)]) >0

Does that help?