Multi-field <if-then>

beperron

Greetings:

I'm coming from an R background where I do all my data cleaning preparation and trying to find my legs with Alteryx. I'm a single day into the experience, so my apologies for a very basic question.  I have used a dynamic selection to grab 9 unique fields of interest. These fields all contain a specific value that denotes a missing response.  The value for each of these fields is 9999.  I want to apply a formula to all these fields that replaces 9999 with a null value.  It seems that I need a "multi-field" formula using a condition if-then statement.  

The formulat has the following pattern:  

IF c THEN t ELSE f ENDIF

I'm not sure how to specify the condition (9999) for all the variables!  Seems straightforward if it is a single field, but I am not seeing the specification for the multi-field option.  Any guidance would be greatly appreciated!

Brian

patrick_digan

Great question! The Multi-Field Tool would be the best.Assuming it's a text field, I would go with this:

IF [_CurrentField_] = '9999' Then Null() Else [_CurrentField_] ENDIF

If it's not a text field, then eliminate the single quotes. Be sure to uncheck the box about copy outfields and add _new as suffix (which is the default). For what it's worth, I find the Switch or IIF functions work quite nicely as well, which helps avoided nested ifs if you were to expand to more things you were searching for:

IIF([_CURRENTFIELD_] = '9999',Null(),[_CURRENTFIELD_])

 or 

Switch([_CURRENTFIELD_],[_CURRENTFIELD_],'9999',Null()) 

Hope that helps!