CODEWITHSUNDEEP
if-statementcalculated-columnsspotfire
Philip Connell
Philip Connell

Reputation: 651

If statement not working in Spotfire

I hope you can help I am attempting to use an If statement in Spotfire. What I am trying to achieve is this

I have 13 unique numbers and what I am trying to say is that if column [GL_ACCOUNT(2)] = any of these 13 numbers then return me "Not EFPIA" in my new calculated column 'GL Account Filter'

It works up to two numbers but once i increase the amount of numbers the formula will not work.

My Code is below. As always any help is greatly appreciated

if([GL_ACCOUNT(2)]="0063304000","0063401000", "0062001000", "Not EFPIA")

Upvotes: 1

Views: 3433

Answers (2)

Vivek Kumar
Vivek Kumar

Reputation: 66

The expression can be used as 

if([GL_ACCOUNT(2)] in "0063304000","0063401000", "0062001000", "Not EFPIA")

Upvotes: 0

S3S
S3S

Reputation: 25122

Without using TERR or JS or IronPY you'll have to explicitly OR these together. I think you are trying to do something like the IN clause in TSQL as explained here but I'm unaware of that functionality in Spotfire.

if([GL_ACCOUNT(2)]="0063304000" or 
   [GL_ACCOUNT(2)]="0063401000" or 
   [GL_ACCOUNT(2)]="0062001000", "Not EFPIA")

You can also do this with a CASE if that's more legible for you.

case
   when [GL_ACCOUNT(2)] = "0063304000" then "Not EFPIA"
   when [GL_ACCOUNT(2)] = "0063401000" then "Not EFPIA"
   when [GL_ACCOUNT(2)] = "0062001000" then "Not EFPIA"
   else NULL
end

Or with the OR....

case
   when [GL_ACCOUNT(2)] = "0063304000" OR
        [GL_ACCOUNT(2)] = "0063401000" OR
        [GL_ACCOUNT(2)] = "0062001000" then "Not EFPIA"
   else NULL
end

Upvotes: 1

Related Questions