CASE statement in Calculated column

[Former Member]

Hi Frzz,

I have below requirement in Calculated column with CASE statement. Could some one help me how to achieve this with case statement.

String  =   0Hello

                01Hello

                012Hello

                0123Hello

If  1st Character of the string is '0' then  -  0Hello

    1st 2 characters of the String is '01'  -   22Hello

    1st 3 characters of the String is '01'  -   333Hello

    1st 4 characters of the String is '01'  -  4444Hello

Thank you.

Best Regards,

Krishna.

[former_member182302 · Accepted solution]

Hi Krishna ,

Using IF and MATCH to do that: ( I took one of the conditions you specified )

IF(match("STRING1",'??0??'),'333Hello',"STRING1")

Output:

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Can we do it with CASE???

[Former Member · Accepted solution]

Case does not work in calculated fields in Analytic View only option you have here is to use IF statement instead...

[Former Member · Accepted solution]

Hi,

Am creating the calculated attribute in Calculation view but not in Analytic View.

My question is can we use CASE statement to achive above requiement with out IF ELSE

[Former Member · Accepted solution]

Hi Krishna G,

For Calculation views that are created as graphical it works its exactly the same. So you can not use Case in it..

For Script based Calc view you can not use Case in Procedural script but cal be used in a select statement embedded in it.

For more detail check Henrique's reply in the post below

[Former Member · Accepted solution]

HI,

Am using Calculated column...with in that am using CASE....but not Scriped CV.

Best Regards,

Krishna.

[Former Member · Accepted solution]

Hi Krishna,

If i have to use multiple IF statements like above case. how can i write multiple IF statements??

Can i write below??

IF(midstr("STRING1",1,1),'0',"STRING1")

IF(midstr("STRING1",1,2),'01','22'+rightstr("STRING1",6))

IF(midstr("STRING1",1,3),'012','333'+rightstr("STRING1",6))

IF(midstr("STRING1",1,4),'0123','4444'+rightstr("STRING1",6))

Best Regards,

Krishna.

[former_member182302 · Accepted solution]

Hi Krishna,

Please find the syntax below:

case ( match("STRING1",'??0??'), '333Hello')

Regards,

Krishna Tangudu

[former_member182302 · Accepted solution]

Yes, you can do in multiple ways. ( As shown above)

1) Using IF with MATCH

2) Using Case with MATCH

and even using midstr

If I understood your requirement correctly, then this should be the correct way of representing as shown below:

IF(midstr("STRING1",3,1),'333Hello',"STRING1")

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks for your update.

My question is how to use multiple IF statements to fulfill above requirement.

Best Regards,

Krishna

[former_member182302 · Accepted solution]

You can use like this:

IF(midstr("STRING1",3,1),'333Hello',IF(midstr("STRING1",4,1),'4444Hello',"STRING1"))

Addup how many conditions you have in the else part like shown above

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks a lot. This is what am looking for.

Best Regards,

Krishna.

[Former Member · Accepted solution]

Hi Krishna,

If i want to implement IF ELSE kind of statements in Calculated column, how can i achieve it. In your last reply you mentioned how to write multiple IF statements.

Scenario:

IF ( 1st Character <> 0 ) -- >  Yes -->  Do some calculation

                                        --->  NO ---> < IF (Numeric )  --> YES--> Convert to String->Lpad->IFs(con)

                                                                                        --> NO  --> Lpad-->IFs(condition)

Best Regards,

Krishna.

[Former Member]

Hi everyone,

I've looked through the previous examples and think that mines is a bit different because it is an integer data type. Below is my statement. I am trying to create a calculated column based on ALERTID, for integers between 2051-2073 I want the text 'Turkish to be displayed', then for 1-2050 i want it text to be 'INIT'.

case("ALERTID",2051-2073,'TURKISH',1-2050,'INIT','NONE')

Can someone show me how this code should look? I don't get an error but I don't get the correct

results.

Thanks,

Connor Nelson

[Former Member]

Hello Nelson,

The above scenario which you have mentioned using case might not use a particular range eg(1-2050),it is considered as a MINUS but for a range. So i have rewritten another code using "nested IF" statement.

if("ALERTID"<=2050,'INIT',if("ALERTID">2050,'TURKISH','NONE'))

Hope it is helpful.

Thanks,

Krishna

[Former Member]

Awesome, I was able to take what you did and alter it a bit so it does ranges.

if("ALERTID">2050 and "ALERTID"<2074 ,'Turkey',if("ALERTID">2074,'South Africa','NONE'))

Thank you Krishna

[Former Member]

Anytime Mr.Nelson , Always happy to help !!

Regards,

Krishna

[Former Member]

If  1st Character of the string is '0' then  -  0Hello

    1st 2 characters of the String is '01'  -   22Hello

    1st 3 characters of the String is '012'  -   333Hello

    1st 4 characters of the String is '0123'  -  4444Hello