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

Multi Field Primary.PNG

I'm trying the Multi Field tool without success.

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

Multi Field Primary script.PNG

Thanks for some guidance on this.

Brenda

Common Use Cases

Accepted answers

JohnJPS

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

All comments

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.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335