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

Input value if else

Istwineres
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

8 REPLIES 8
Luke_C
17 - Castor

@Istwineres 

 

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. 

mceleavey
17 - Castor
17 - Castor

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.

 

Thanks.

 

M.



Bulien

Jon_Taylor
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?

Istwineres
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!

Luke_C
17 - Castor

@Istwineres 

 

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.

 

Luke_C_0-1616524707514.png

 

bpatel
Alteryx
Alteryx

hi @Istwineres 

 

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

bpatel_0-1616525072738.png

hope this helps

 

AdamR_AYX
Alteryx Alumni (Retired)

Another option is

 

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

 

Attached an example

Adam Riley
https://www.linkedin.com/in/adriley/
estherb47
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.

 

Cheers,

Esther

Labels