Looking for info on Between

[Anthony Alvarez]

I want to know how to use Between.

When I type Between into an expression editor it shows up in blue text. I searched in the help file, but not much on it.

The person that asked me about Between said an AI program (Chat GPT I think) told them to use it in a case statement like the one below. The below expression throws an error "The expression is not valid after 'between' on line 2 character 8."

case 
  when between([Data], 1, 2) then TRUE 
  else FALSE 
end

I know between can be used in SQL. I 'm not sure how to use it in Spotfire custom expressions or calculations.

 

When I want to check if a value is between two numbers I usually write it like this:

case 
  when [Data] >=1 and [Data] <= 2 then TRUE 
  else FALSE 
end

Maybe between can simplify expressions in the future.

[David Boot-Olazabal]

Hi Anthony,

I see you are trying to use the 'between' operator when creating a  calculated column.

I know the between operator can can be used in plain SQL, which makes it suitable to use in Spotfire's information link query (see example below), but it's not listed as an operator in Spotfire when creating a calculated column. Hence, the blue between when creating such a case statement.

Even though AI may know a lot, one needs to be cautious of the outcome (it should be verified/tested). And your work around is probably the best way to achieve the same.

If a between operator would be beneficial, I would suggest to create an enhancement request via https://ideas.spotfire.com/ideas.

Kind regards,

David

Edited July 12 by David Boot-Olazabal

[Olivier Keugue Tadaa]

Hi Anthony

Please would you be kind enough to add this as an idea on our idea portal https://ideas.spotfire.com/? 

We don't yet have that operator but you can simplify your expression with the IF function like this

If([Data] >=1 and [Data] <= 2,true,false)

[Anthony Alvarez]

David and Olivier - Thanks for your comments. I've created the idea: https://ideas.spotfire.com/ideas/TS-I-9674

[David Boot-Olazabal]

Thanks Anthony, I voted up your idea.