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 · Accepted solution]

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!

[beperron]

Perfect, and thanks for the additional suggestions on the specification!  I got up to the point of replacing values and simply couldn't crack `Null()`!  Much appreciated.

Brian

[acaryasin01]

Hi @patrick_digan 

I saw your very old solution,

based on this, I would like to ask to you,

How can I use Multi-Formula tool to reach something like this:

3 column names are Delay reason 1 / Delay Reason2 / Delay reason3

IF [_CurrentField_] = "External" THEN [_CurrentField_]
ELSEIF [_CurrentField_] = "Internal" THEN [_CurrentField_]
ELSEIF [_CurrentField_] = Null() THEN [_CurrentField_]
ELSE [_CurrentField_] = "External-Internal"
ENDIF

I tried this formula but gave error : 

Invalid type operator ==,

When I deleted =, at this time, it gave different  error :

Malformed If Statement

Can you help me about this?

[patrick_digan]

Hi @acaryasin01 I just saw your post. I'm a little confused at what you're trying to accomplish, perhaps you can provide an example? The multi-formula tool can apply the same formula template to multiple fields and reference the current field value or name. As for your error, the last else argument needs to just be a string, perhaps something like: else "External-Internal" endif. In looking at your logic, for each selected field it will check if it's equal to "External","Internal" or Null. If it is, then it will result in External/Internal/Null. Otherwise, you want to return External-Internal? Perhaps this would work:

If [_CurrentField_] IN ("External","Internal",Null()) Then [_CurrentField_] else "External-Internal" ENDIF