ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Input value if else

8 - Asteroid

Hi, I've a column with social security number where if the value is not present the info provider could write: 0000000000000000  (range of zeros) till "" (empty) as string, I was wondering if there's a better way than if ssn="0000000000000000" or ssn="000000000000000" or ... until or ssn="" to handle this situation. thanks

12 - Quasar



I'm not sure I fully understand the ask. Are you looking to replace empty values or identify instances where the provider put all 0s? SSNs are usually 9 digits so if they're sending you that many 0s you can just use a length function to identify records that exceed the usual data (assuming the data is clean). 


If possible attaching sample data and expected results might help the community. 

14 - Magnetar

Hi @Istwineres ,


can you post some mock data representing what you're trying to along with the records that don't have "000000000", and we can build it for you.





8 - Asteroid

Hi, A few Questions that may help bring a solution.


Are you trying to use an if formula to say if a row in the column is blank then give a SSN as all zeros?


Are all SSNs you have the same number of digits or string characters say all SSNs are 7 numbers xxx-xxx-xxxx.or xxxxxxx

             if this is the case i think in an "if than formula you can have it give 7 - 0's instead of 1 null 0.

Are you asking if a SSN is incomplete 12x-123-1234 shows as 120-123-1234?

8 - Asteroid

Hi, the input value could like this:

ssn = "0000000000000000"

ssn = "000000000000000"

ssn = "00000000000000"

ssn = "0000000000000"

ssn = "000000000000"

ssn = "00000000000"

ssn = "0000000000"





ssn = "0"


if those are the input values I'd like to replace with an empty string. Thanks!

12 - Quasar



You can try regex: \<0+\> that checks if the record is all 0s. See attached, I ran a few different checks, but of course validate against your data.





hi @Istwineres 


you could also change the data type from a string to a number and do a if than statement like this


hope this helps



Another option is


iif(ReplaceChar([ssn], "0", "")=="", "", [ssn])


Attached an example

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
15 - Aurora
15 - Aurora

Hi @Istwineres 


Looks like you have at least two solutions here that will work. I've gone ahead and marked them as such so that when others have a similar question, they'll get to an answer.


If you'd like, please mark other solutions that work as well.