Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

brendafos
10 - Fireball

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

 

7 REPLIES 7
JohnJPS
15 - Aurora

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
10 - Fireball

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
15 - Aurora

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

brendafos
10 - Fireball

That fixed it.  THANKS.

 

So much to learn.

JohnJPS
15 - Aurora

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
7 - Meteor

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
10 - Fireball

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.

Labels