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

Empty String Field

dshaw
8 - Asteroid

I need formula help.  I have a string field called "Market" that has some blank rows.  What's the best formula or tool to use to replace empty or blank rows with text?  So, if "Market" empty, then "NIC".  I am not sure what I am missing.  Thanks.

6 REPLIES 6
jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest using a formula tool with an expression:

IIF(IsEmpty([Market]), "NIC", [Market])

If you expect empty whitespace as well then use:

IIF(IsEmpty(TRIM([Market])), "NIC", [Market])
MarqueeCrew
20 - Arcturus
20 - Arcturus

@dshaw,

 

Because my friend James (@jdunkerley79) is a big fan of RegEx expressions, I couldn't resist adding an expression here:

 

REGEX_Replace([Market], '\0|^\s+$', "NIC")

The pattern for replacement above looks for a NULL or a string that is only space(s).  If it finds either case, it will replace that with NIC otherwise it leaves it alone.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Chrispy
7 - Meteor

If there's the possibility that the blank rows are Null (as opposed to empty), you can add in a check for both:

 

if ( IsNull([Market]) or
IsEmpty([Market]) ) then
"NIC" else
[Market] endif

This will insert "NIC" if the field is either blank or Null, and keep the text present in the [Market] field if it's not.

dshaw
8 - Asteroid

Thank you all! I appreciate the diverse ways of looking at it.  No matter how many times I have done it I seem to always get tripped up on the Else Endif.  Some how all my saved formulas were erased when I upgraded to 11.  Is there anway for me to get them back?

dshaw
8 - Asteroid

I appreciate this one too so is the difference between using "IIF" vs "IF" is that I would not have to remember to use ELSE and ENDIF?  I have used this in Tableau but I really did not know the difference.   Thanks.

scbrownsr
6 - Meteoroid

This formula came in very handy for me today, so thanks!!

Labels