Input value if else

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

Developer

Developer Tools

Common Use Cases

Accepted answers

Luke_C

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

Remove0.yxmd

AdamR_AYX

Another option is

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

Attached an example

ReplaceZeroStrings.yxmd

All comments

Luke_C

@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

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.

Jon_Taylor

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

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

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

Remove0.yxmd

bpatel

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

AdamR_AYX

Another option is

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

Attached an example

ReplaceZeroStrings.yxmd

estherb47

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703