Formula Question, why I get 0 as result

[Williamcai]

Here is the simple formula. 

I have a data column BUCKET, contains only 4 possible V_WString values: C, R, B, F. I want to collapse them into 2 values, C and R go to Realized, B, F go to Forward. But I ended up with all 0 in the column. 

IF [BUCKET] in ("C","R")
THEN [BUCKET] = "Realized"
ELSE [BUCKET] = "Forward"
ENDIF

[Thableaus · Accepted solution]

Hi @Williamcai 

You don't need to mention your field again in the response of your IF statement.

This would work:

IF [BUCKET] in ("C","R") 
THEN  "Realized"
ELSE "Forward"
ENDIF

Cheers,

[Thableaus]

@Williamcai 

The reason you get 0's is because it interprets this as a True/False outcome:

[BUCKET] = "Realized"

Bucket is NOT equal "Realized", so it returns False (0).

0 means False, -1 means True.

Cheers,

[Williamcai]

Thank you for the explanation. @Thableaus 

[chris_rowntree]

Thanks!

[DanielG]

I know this is old, but wanted to add to it.  You will see this even if your "qualifier" isnt the same field as the one you are modifying.

Formula was created on the Field2 column within Formula Tool

If [Field1] = "XXX" then [Field2] = ""

else

[Field2]

endif

and it was still returning a zero instead of following what I thought was correct syntax.  By removing the first reference to [Field2] and just having:

If [Field1] = "XXX" then ""

else

[Field2]

endif

So simple, but I was stuck on it for a while myself.  Hence searching and finding this solution.  Thanks to @Thableaus 🙂