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
Solved! Go to Solution.
@chiragpatel_1 try this
IF IsEmpty([Report Notes]) or Isnull([Report Notes]) OR ToNumber([Report Notes]) <= 1 then 0 else 1 endif
Didn't work unfortunately, thanks for the suggestion
@chiragpatel_1 what doesn't work?
the formula you suggested, output is still all empty
actually what do you want the output to be integer or string?
Integer, thanks
try updating it to this
IF IsEmpty([Report Notes]) or Isnull([Report Notes]) OR ToNumber([Report Notes]) <= 1 then "0" else "1" endif
and set the output to string and then try using a select tool afterwards to convert it to integer
@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)