If Else Statement Data type error

Question

I have a file and I want the data in which only the values 0,1,2 should be displayed and other data should be blank.

Input Data:                                               OutPut Data

A         B                                                 A                B

Sam      1                                              Sam              1

John      0                                             John              0

Tom      4                                             Tom

Dan       2                                             Dan                2

Ravi      7                                             Ravi

Sunny    0                                            Sunny             0

Han       1                                            Han                 1

This is the output I want,  I am trying with If Else condition :

IF [B] = 0
THEN "0"
ELSEIF [B] = 1
THEN "1"
ELSEIF [B] = 2
THEN "2"
else ""
endif

This statement is giving parsing error due to string data, how can I solve this ?

cjaneczko · Answer

Quotes are used in String fields. If it is a numeric field you want to remove the quotes from your statement.

SPetrie · Answer

What data type is column B?

Also, setting it to "0" etc with quotes, is typecasting the result as a string. If the column is a number, you cant save a string in that column.

You are testing against 0 without quotes, which is a numerical zero and then trying to save it as a string zero with the quotes.

If its a string, you can use if ToNumber([B]) >2 then "" else [B] endif

If its a number, you can use if [B] >2 then null() else [B] endif

griffinwelsh · Answer

You need to keep your data type consistent for comparison and also match the output data type for the column. You can also simplify your expression using an IN statement. See the expression below:

IF [B] IN ("0","1","2") then [B] else "" endif

Note if you have column B as a numerical data type (INT, Double, Float ect) you need to change the data type using a select tool before feeding into this expression. This is because you cannot have a blank string as a numerical data type. If you want to use a numerical data type use null() instead of "".