We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
14 - Magnetar

@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
16 - Nebula

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.

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
14 - Magnetar

@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
Alteryx
Alteryx

Another option is

 

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

 

Attached an example

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
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