Conditional Statements: Using IIF instead of IF THEN

[JeradR]

One trick I often use to conditionally update a field is the Boolean IIF function.  IIF is basically a shorthand version of a single condition test.  While the documentation suggests that you’d be performing the function against a Boolean field, you can also create the Boolean test within the expression.

For example, you’d like to populate a string type designator field based on numeric data like sales – Stores with sales numbers below $1,000,000 are high risk stores.  A simple IF/THEN is a typical way to do this, but the Boolean IIF function can do the same thing and it’s much quicker to write out!  See below and the attached v11.0 workflow example.

IF [Sales]<1000000 THEN "High" ELSE "Low" ENDIF

vs.

IIF([Sales]<1000000, "High", "Low")

Jerad Rades

Customer Support Engineer

[jordanmount6]

I am using an action tool to update a value in a dynamic input sql editor and I'm using IIF for the formula. What i want to accomplish is to have the user use a check box to update the sql. if it is unchecked i want that string in the sql code to be deleted since Alteryx doesn't like commented out lines. 

Is there a solution to this? 

I've tried both of these formulas:

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ' '))

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ''))

This is the line in the SQL editor: 

--and PAYMT_RATING = 'Rating'

The "'Rating'" is being updated by a different Text Box -> Action -> text input tool. 

[ColinG]

I am having trouble using IIF or IF/THEN/ELSE in a formula tool. I'm trying to conditionally update a field in a formula tool. I have chosen the existing "vendor_note" field as the output column.

This is the formula:

iif(IsNull([vendor_note]),[Upload_Note],iif(IsNull([vendor_note]),"",[vendor_note])+" "+[Upload_Note])

The input result panel shows "vendor_note" field as [null]. But the output result panel shows the "vendor_note" as false. So not only does it seemingly wronly identify the vendor note as not null when it does show incoming null data, instead of attempting to rewrite the field contents to the concatenated contents of the vendor note and upload note, it just displays the value of the first null test. 

[lepome]

@ColinG 

Your formula does not make sense.
It is equivalent to saying

If [vendor_note] is null, then use [Upload_Note] here.

Otherwise (when [vendor_note] is not null)

     when [vendor_note] is (simultaneously not null and) null ...  <-- logical imposibility

At this point, Designer gives up.

What you probably meant was IIF(IsNull([vendor_note]),[Upload_Note],[vendor_note]+" "+[Upload_Note])
which would work unless either field is numeric.

[ColinG]

@lepome 

Both columns are text strings but can be null. Your suggestion is exactly what I first tried, as it would work similarly in many programming languages. Unfortunately when the incoming vendor_note field is null, your suggestion errors in Alteryx returning a:

ErrorLink: Formula (48): https://community.alteryx.com/t5/*/*/ta-p/34149?utm_source=designer&utm_medium=resultsgrid|
Parse Error at char(54): Type mismatch in operator +. (Expression #1)

My formula does run without an Alteryx error, it just evaluates incorrectly. But if I have the formula write to a new column like vendor_note2, then my formula does evaluate correctly. There is something about how Alteryx is performing and storing the intermediate calculations before rewriting the original column data, that is causing it to give inaccurate results in the original column only.

As to why I had to write that (silly looking) formula that way, It appears when the first conditional test is true, Alteryx still calculates the value that the false conditional test would resolve to, even though it will not be used. Since you can't concatenate with a null value, I discovered by again forcing the null value to an empty string before the concatenation, the calculation would succeed in the background and not return an error, even though it would not be output. 

This could be solved other ways too than rewriting to a new column, like setting null values to an empty string in an earlier formula tool so I don't have to do the duplicate isnull check. It is just an oddity and frustration that took me 3 hours to figure out what was happening and ways to work around it.

---

[lepome]

@ColinG 

Fascinating!  You correctly surmise that I did not actually replicate what you reported before commenting.  That is my bad.

I wish I could say that I will pursue this topic on Monday. But I know that I will prioritize other things that don't have a ready workaround and topics that I previously committed to pursuing.  For me, it's an interesting tidbit that I appreciate learning from you.

[ColinG]

I discovered more information about my issue. Prior to running this IIF statement that errors out or returns the wrong result, to ensure that the vendor_note field exists in the inbound data, I had a text input tool with a field called "vendor_note" and append tool. The vendor may or may not provide a note for us to upload. The text input tool field "vendor_note" content was blank. This is the common workaround I see on the Alteryx forums to deal with checking if a field exists in a data flow, and if not, create the field.

There are some bugs in the text input tool that I didn't realize. It looks at the field contents and assumes a data type. There is no way to force it to be one type or another, besides adding a formula tool right after it that specifies the type you want. I discovered this by clicking on the exiting connection. I haven't seen a post on the forums where this workaround for checking for missing fields, includes a caution of using a formula tool to force the correct data type. Because that text tool field "vendor_note" contents was blank, the input tool assumed it was a bool.

Unluckily because I happened to choose the #1 union input as the text tool, instead of the rest of the inbound data flow, the union tool created the field "vendor_note" as a bool. Then later in the flow, when my next formula tool was running the IIF statement, even though I chose the output as a v_string, Alteryx was unable to make the conversion during the in situ field calculation of the results. This is why with the more obvious solution @lepome suggested I was getting a type mismatch, and with the workaround I came up with I was getting the text representation of the bool result. The formula tool can only calculate it correctly if it is a new field with a new data type specified.

This problem and the solution was quite unintuitive and an unfortunate time sink.

[lepome]

@ColinG 
You are fundamentally correct.  The only detail I would mention for future readers is that if the field data type is important, using a Select tool immediately after a Text Input will help (if you are not extremely conscious about how you specify the values in that tool).  Congratulations on figuring out the root cause of your interesting observation, and thank you for reporting back!