Hello All,
I am extremely new to Alteryx and have ran into an issue while trying to cleanse some data. The data I am cleansing has singular numbers in a few of the cells. It is just the number "2". I am attempting to replace all of these 2s with a null field. I added a Multi-Field Formula with the expression:
IF [column-name0]==2 THEN NULL()
ELSEIF [column-name1]==2 THEN NULL()
ELSEIF [column-name2]==2 THEN NULL()
ELSEIF [column-name3]==2 THEN NULL()
ELSEIF [column-name4]==2 THEN NULL()
ELSEIF [column-name5]==2 THEN NULL()
ELSEIF [column-name6]==2 THEN NULL()
ELSE [_CurrentField_]
ENDIF
I am very embarrassed, again, because I am so new to this software. However, I was hoping this expression would go down each of my columns and change any singular 2s to nulls. If there is not a 2 in the field, then I wanted the information to be left as is. This has not been the case. When ran, it makes every cell in a row with a 2 null. When I run the above expression, it changes every cell in the column to null.
If anyone could recommend a fix to me, or explain to me why the entire row becomes null, I would GREATLY appreciate it. Thank you in advance!
Solved! Go to Solution.
You're gonna kick yourself, the formula you want is just this:
IF tostring([_CurrentField_])=='2' THEN null()
ELSE [_CurrentField_] ENDIF
You are getting those results because you're specifying rules like "IF 501C5==2 then null()", even when the [_CurrentField_] may be your [Account Line Item] field. So the end result is effectively "501C5==2 then [Account Line Item]==null()"
Edit: I added the tostring( just because I was applying this to "All Type of" fields in my test and that forces it to work.
Wow, thank you SO much. This worked perfectly and your explanation makes a lot of sense. I greatly appreciate it!
Happy to help! Also, congrats on your first post. I hope this inspires you to stay involved and keep growing with the rest of us.