Formula Tool Generating Number instead of specified Value

[asmith314]

Here is my formula

IF [AbnormalFlags] ='' AND [Cotinine]='NEGATIVE'   THEN [AbnormalFlags]='N'
ELSEIF [AbnormalFlags] = '' AND [Cotinine] = 'POSITIVE'  THEN [AbnormalFlags] = 'A'
ELSE [AbnormalFlags]
ENDIF

It appears to be like it is working as expected in the Preview window

But in the Output it is generating 0's instead of the value

Here is the data flowing into the formula tool

Here is the data flowing out of the formula tool

It should be showing N not 0

What am I doing incorrectly?

[DataNath · Accepted solution]

Hey @asmith314, because you're putting THEN [FieldName]='X', Alteryx is performing a check to see if this is true (0 or -1 boolean), rather than assigning it the outcome you want. Also, checking for AbnormalFlags being '' will trigger wrong with null values and so you're best of using IsEmpty() to check this, which also handles nulls.

How do you get on using this instead?

IF IsEmpty([AbnormalFlags]) AND [Cotinine]='NEGATIVE' THEN 'N'
ELSEIF IsEmpty([AbnormalFlags]) AND [Cotinine] = 'POSITIVE' THEN 'A'
ELSE [AbnormalFlags]
ENDIF

[asmith314]

Perfect! Thanks

[asmith314]

I did have blanks and NULL's and to deal with the NULL values I added a multi-formula tool to change those NULL's to blank spaces. This solution allowed me to remove that tool and it handles both nicely. I had no idea that IsEmpty handled NULL's.

Thanks