Find Not Null Cells Within a Column and Replace with X

[NicoleMA]

I'm very new and not good with the expressions so I can't figure out how to find any cells within a column that contain text (are not null) and replace that text with "X". I thought find and replace would be the way to go, but couldn't figure that out. Then I thought it would be just an expression in the formula tool, but I don't know how to edit the expressions to be anything other than they read. I was thinking: IF c THEN t ELSE f ENDIF?

In the same below I want to replace (San Jose) with an X and leave the other cells in that column blank.

ZIP	Sales	SPH	Home ZIP
60126	2000	100
60515	200	200	San Jose
60559	500	20	San Jose
85210	10000	700

Hopefully this IS as easy as I thought and someone can help me out! Thanks!

[JohnJPS]

Hello - you could set it to replace the existing value (e.g. for Home Zip), and in the Expression, try:

IF (IsNull([Home ZIP])) THEN

   Null()

ELSE

   "X"

ENDIF

[NicoleMA]

It throws an error and says "Unknown funtion "IF".

[JohnJPS]

I'm not sure why that would be.  I've attached a small workflow that should hopefully illustrate it better.

[NicoleMA]

Thanks, but I don't know how to open this. It opens in an internet tab and I can't right click to save it as a module it just has XML.

[JohnJPS]

Yep - does that for me to.  If you right-click on it and select "Save As" then it will save as a .yxmd file on your machine; then double-click to open in Alteryx.

[NicoleMA]

hmm I really don't get it then. When I right click all I get is the option to save the picture. I can't change the file type to anything other than XML or if I literally click on the picture Icon I can naturally save that as JPEG or Btmp but that is obviously not what I am trying to save!

I copied the expression you told me verbatim.

Thanks for trying!

[JohnJPS]

Please try right-clicking the little blue icon (not the paper clip image); then save that.  Hopefully that will take.  :-)

[PamW · Accepted solution]

Try this

IF IsEmpty([Home ZIP]) THEN
   Null()
ELSE
   "X"
ENDIF

If the field is truly null you can use the following:

IF IsNull([Home ZIP]) THEN
   Null()
ELSE
   "X"
ENDIF

Note that in the below post there was an extra parenthesis before the IsNull() (shown in bold below)

IF (IsNull([Home ZIP]) THEN

   Null()

ELSE

   "X"

ENDIF

[JohnJPS]

LOL... the one time I just write something out off the top of my head.  Busted.  Good catch and thanks!

(I've now corrected the error, also).