Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula Tool Generating Number instead of specified Value

asmith314
8 - Asteroid

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

 

asmith314_0-1663338505628.png

 

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

 

Here is the data flowing into the formula tool

 

asmith314_1-1663338609168.png

 

Here is the data flowing out of the formula tool

 

asmith314_2-1663338711223.png

 

It should be showing N not 0

 

What am I doing incorrectly?

 

3 REPLIES 3
DataNath
17 - Castor

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

DataNath_0-1663339260246.png

 

asmith314
8 - Asteroid

Perfect! Thanks

asmith314
8 - Asteroid

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

Labels