columns with text and numbers

[chiragpatel_1]

Hi,

Im trying to put a formula in which mimics the excel formula below

=IF(OR(AK2="",AK2<=1),0,1)

AK being the column called 'Report notes', this column has text, numbers and empty cells and has the above formula applied in new column ('rate') of course which works fine in excel but in Alteryx I'm finding it difficult to make it work with the below, the output of below is all nulls.

IF IsEmpty([Report Notes]) OR ToNumber([Report Notes]) <= 1 then 0 else 1 endif

Report notes column is in v_string format fyi, as having it as int32 gets rid of the text.

Have tried all kinds of things with above but no luck, any help will be great.

Thank you

[aatalai]

@chiragpatel_1 try this 

IF IsEmpty([Report Notes]) or   Isnull([Report Notes])  OR ToNumber([Report Notes]) <= 1 then 0 else 1 endif

[chiragpatel_1]

Didn't work unfortunately, thanks for the suggestion

[aatalai]

@chiragpatel_1 what doesn't work?

[chiragpatel_1]

the formula you suggested, output is still all empty

[aatalai]

actually what do you want the output to be integer or string?

[chiragpatel_1]

Integer, thanks

[aatalai · Accepted solution]

try updating it to this 

IF IsEmpty([Report Notes]) or   Isnull([Report Notes])  OR ToNumber([Report Notes]) <= 1 then "0" else "1" endif

[aatalai]

and set the output to string and then try using a select tool afterwards to convert it to integer

[binuacs]

@chiragpatel_1 Maybe you can make your formula apply only to the numeric values

IIF(IsEmpty(Trim([Report Notes])) or (!REGEX_Match([Report Notes], '\D+')  AND toNumber(Trim([Report Notes])) <= 1), 0, 1)