Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

IsNull(v) not recognizing Null fields?

6 - Meteoroid

OK, I am working with data where I need to identify the first column where a Null value appears, example below:2018-07-12 08_36_45-Clipboard.jpg

  I am using a nested IIf statement:

 IIF(IsNull([06_2017]), 1, IIF(IsNull([07_2017]), 2,IIF([08_2017], 3, IIF(IsNull([09_2017]), 4, IIF([10_2017], 5, IIF(IsNull([11_2017]), 6, IIF(IsNull([12_2017]), 7, IIF(IsNull([01_2018]), 8, IIF(IsNull([02_2018]), 9, IIF(IsNull([03_2018]), 10, IIF(IsNull([04_2018]), 11, IIF([05_2018], 12, IIF(IsNull([06_2017]), 13, 0))))))))))))) 


All referenced fields are set to Double, output is set to Double


and I am getting  this type of result - it works yet it doesn't work -

2018-07-12 08_44_00-.jpg


As you can see on the top rows it is outputting the correct column in the # Null column but down at the bottom it is saying that there is a null value in column 3 when there is a number there.  Can anybody tell me what I am doing wrong here?






ACE Emeritus
ACE Emeritus

I think you're just missing an IsNULL() on value 3 :)

right now it says IIF([08_2017],3 which will always evaluate to TRUE because it is basically saying IF 1 = 1.\


EDIT: I also think you're missing one on value 12   IIF([05_2018]

13 - Pulsar

You can also accomplish this through the Transpose tool.  It takes a few more steps, but you don't have to hard-code the conditions in so it's much more dynamic. That way if you end up with additional columns or your field names change you don't have to remember to update your formula:



6 - Meteoroid



Thank you so much for the use of your eyeballs!  Yes I did indeed miss the IsNull function on month 3 and also on month 5 and month 12!  I re-read that blasted statement a hundred times yesterday looking for a boneheaded mistake like that and I still missed it.  Thank you again.