IIF Statement Do Nothing in Else Section

[EtherealNoise]

I'm trying to figure out how to make an if statement do nothing in the else section of an IIF statement. 

Example: IIF(Length([Column5])>40, "color: red;", "color: black;")

I've noticed if I keep the color: black; in else then that could override another validation rule that colored it red previously. So I want it to either color it red or do nothing. I don't want it to make it black. I've also seen suggestions saying to use the [Column5] again in the else but then it's just rewriting the data without any other formatting, which is basically just saying color: black;. 

I've also seen Null() as a solution and while that has helped I've seen some instances of it coloring it red even when it's not supposed to when Null() is there. It's supposed to be black but it's red then when I switch it back to color: black; instead of Null() it sets it to black correctly. So Null() is a little unpredictable.

[FrederikE]

Hey @EtherealNoise,

Can you provide an example where this doesn't work: 

"I've also seen Null() as a solution and while that has helped I've seen some instances of it coloring it red even when it's not supposed to when Null() is there. It's supposed to be black but it's red then when I switch it back to color: black; instead of Null() it sets it to black correctly. So Null() is a little unpredictable."

This should certainly not be the case, and if it is, it would be a bug.  There is very likely an other reason (Meaning the condition is met) if this doesn't result in a Null. 

In General, if the column already pre-exists before your statement, just refer to the column itself. If it doesn't, Null() will do. 

[apathetichell]

Hey @EtherealNoise not following you here. When we talk about field values If and Then always has an Else (ie the original value). In Alteryx this is structured as iif(boolean test,new value,old value)...  If this is a new field you can subsititue null() for old value - this would be like N/A in Excel. 

lf you want to control an action you would use a filter - where only a branch would occur if a True or False condition would be met. In that case the If doesn't need to be written - just use the boolean statement.

Also - random question - are we talking about row/column formatting in a Table tool? I can see some buggy behavior there - but otherwise null() is not buggy.

[EtherealNoise]

Here's an example where it didn't work. 

IIF(Length([Column2])!=2, "color: red;", Null()). This is inside a basic table with a column rule on the column2.

When I put color: black in else it colors it black which is the correct color. But really I just want it to do nothing in the else. I don't want [Column2] there cause it will rewrite the data without any of the formatting so I lose the red if this was flagged for any other reason and by putting color: black in the else it would also overwrite any other rule as well.

But in this example, I don't have any other rules so it shouldn't be red.

[apathetichell]

o.k. - I thought we were talking about column/row rules in a table - this is pretty specific. You may want to recreate a message and clarify that you are talking about that vs generically in a formula. I do remember this being very buggy (like formulas didnt't even work on one version?) but TBH I use these tools so sparingly that someone else probably has more recent real world experience.

[apathetichell]

Hey - I do think a new topic where you clarify that you are talking about customized formatting in a  table will help you out here. Reading your subject line - other community members will think you're just talking about a formula tool IIF statement which is quite different here.

[EtherealNoise]

Ok. I'll do that. Even though it is in a formula section of the rule so it should work like in any other formula section. It seems like what you're saying is the Column Rule formula reacts differently than the other formula sections in Alteryx.