Multi Field - IF != Null() with AND - help needed.

[brendafos]

I used the Cross Tab tool and made some 3 new columns.

Now I want to take the first column, when it is not null and put that value in one of the other 2 new columns

When there is a  No_Value_Specified value then it needs to go to either Primary or Secondary.

If Primary is empty it goes to Primary, otherwise it goes to Secondary. 

In the snap below 'Orthopaedic Surgery' should move to Secondary

and Radiology should move to Primary

I'm trying the Multi Field tool without success.

This does not give errors but writes over values with 0, or -1

Thanks for some guidance on this.

Brenda

[JohnJPS]

Hi @brendafos

Since the MultiField Formula applies to every selected field, and we are distinguishing the target field, we'll need to add that to the IF conditions... could you try this:

IF ([_CurrentFieldName_]=="Primary" ) THEN
  IF (IsNull([_CurrentField_]) AND !IsNull([No_Value_Specified])) THEN
    [No_Value_Specified]
  ELSE
    [Primary]
  ENDIF
ELSEIF ([_CurrentFieldName_]=="Secondary" ) THEN
  IF (IsNull([_CurrentField_]) AND !IsNull([Primary]) AND !IsNull([No_Value_Specified])) THEN
    [No_Value_Specified]
  ELSE
    [Secondary]
  ENDIF
ELSE
  [_CurrentField_]
ENDIF

[brendafos]

Unfortunately this does not work.  It does not give an error, and does nothing to the output.

I played with it and see that it never drops into the nested IFs.  It goes immediately to the Else in each nested if.  I tested this by putting 'error' in the Else values, and that does write to the output.

I played with trying to fix it but did not succeed.  I think the !IsNull ( [No_Value_Specified]) seems to not catch. 

Thank you for this help.  I think it would be useful if there was a better write up on how to use the _CurrentField_ values.  I've not seen a good explanation of this. The Multi-Field tool does not have any examples. 

[JohnJPS · Accepted solution]

You could also try IsEmpty() rather than IsNull(), to see if that helps.

[brendafos]

That fixed it.  THANKS.

So much to learn.

[JohnJPS]

Sounds great!  Actually I had never thought to use the Multi-Field formula like this (to update specifically targeted fields all at once), so I learned something too.  :-)

[MRioux]

Do you have any other tricks? I have a similar situation:

If my SCP Vendor column is not null, I want to update a new column [In Scope for Wave 5] with "No". I've tried both of these examples but it just leaves it blank. Help!

IF IsEmpty([SCP Vendor])THEN Null() ELSE "No" ENDIF

IF IsNull([SCP Vendor])THEN Null() ELSE "No" ENDIF

[brendafos]

You might try doing a data cleanse on the field first.  It may have a blank space there in which case the IF would not work.

Or check the data types for the fields - they should match.