Calculated Columns using IF statement

[jonathan delucia]

Hello, I have one column titled Period and within that column i have P01, P02, P03,.......P12. I am trying to add another column that says, IF( [Period column] = "P01","Q1", IF( [Period column] = "P02","Q1",IF( [Period column] = "P03","Q1", IF( [Period column] = "P04","Q2",0)))).

I also tried to use the Find function buti couldn't find a solution with that either...

Something that in excel would be pretty easy but i can't figure out how to translate something like this into Spotfire.

Can anyone help

[Sean Riley]

That exact expression won't work becuase the resulting values are inconsistent: some are strings like "Q1" and 0 is an integer. To resolve you would need to make your 0 a string by wrapping it in quotes, like:

 

IF([Period column] = "P01","Q1", IF([Period column] = "P02","Q1",IF([Period column] = "P03","Q1", IF([Period column] = "P04","Q2","0"))))But that might just be an example expression...

 

You can use nested If() functions but it is very inefficient and confusing. It is better to use a case statement in situations like this:

 

case

when [Period column] = "P01" then "Q1"

when [Period column] = "P02" then "Q1"

when [Period column] = "P03" then "Q1"

when [Period column] = "P04" then "Q2"

else "0"

endAnd you can add 12 when clauses as needed

[Manoj Katasani]

Hi Sean ,Appreciate your answer...!  it works for me ,I have case statment in below pattern could you please help me    CASE

                                WHEN Column1 IN ('C','F') AND COLUMN2 = '1' THEN  1

                                WHEN Column1 IN ('K','M','L') AND COLUMN2 = '1' THEN 2

                                WHEN Column1 = 'T' AND COLUMN2 = '9' THEN  3

                                ELSE  4

                               END AS COLUM3There is an and condition in when statement ....Thanks & regardsManoj.K 

[NISHANTH JAIN]

Hello,

Best way to do this is using Case statement as below:

case when [Period column] = "P01" then "Q1"

when [Period column] = "P02" then "Q1"

when [Period column] = "P03" then "Q1"

when [Period column] = "P04" then "Q2"

else "0" end