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

If formula with length of field

ejohnson
5 - Atom

I want to write a formula that says that if the length of a field is not 10, to then give back an invalid result.

 

I've tried to do this by creating a new field through the formula function and use the 'if' formula - but I don't know where to put the 'NOT' in...

 

IF Length([Tax number]) NOT "10" THEN "Invalid" ELSE " " ENDIF

 

This just leads to malformed if function error.

5 REPLIES 5
BenMoss
ACE Emeritus
ACE Emeritus

Hi @ejohnson 

 

For NOT EQUAL I would use !=

 

Also length returns a numeric value not string value, so 10 should not be in quotes.

 

IF Length([Tax number]) != 10 THEN "Invalid" ELSE " " ENDIF

 

Ben

NJT
11 - Bolide

 

Try this out IF Length([TaxNumber]) != 10 THEN "Invalid" ELSE "" ENDIF if the result your looking for is invalid or blank.

 

Capture.PNG

ejohnson
5 - Atom

Perfect! just needed that != syntax :-)

JonquilP
5 - Atom

HI NJT,

 

For your example, what if I wanted to pull the detail from the first column to the new column if it meets my condition? What would insert for Then and Else?

 

For example, Column A Line 1 has 123456789. If I wanted to say If Length of Column A = 9, then place 123456879 in Column B Else "" ENDIF.

 

Thanks so much for your help.

NJT
11 - Bolide

I'm guessing you've figured this one out by now, sorry I didn't get a notification that there was another post here... You can just substitute the "" with your field name there.  

Try this out IF Length([ColumnName]) != 9 THEN "Invalid" ELSE [ColumnName] ENDIF if the result your looking for is invalid or the column name.

 

Labels