Alteryx Designer Discussions

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

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Multiple IF and Startswith assessment

Highlighted
5 - Atom

Hello,

I have a process, where I have to validate that a column contains 9 characters. Normally, the value has 8 numbers and 1 letter. The letter can be at the beginning or at the end of the value. If the value does not have 9 characters I have to add a "0" at the beginning of the value, however, when the letter is at the beginning I need to add the 0 after the letter. for instances: A1234567 then I need the new value as A01234567.

 

I have tried with the formula IF StartsWith([Tax Number 1], "A") AND Length([Tax Number 1])=!9 THEN Replace([Tax Number 1], "A", "A0") ELSE [Tax Number 1] ENDIF and it works fine but, just for one option.

I mean, if I enter the formula as follow, I get a value of "0" instead of the value needed.

IF StartsWith([Tax Number 1], "A") OR StartsWith([Tax Number 1], "B") OR StartsWith([Tax Number 1], "C") OR StartsWith([Tax Number 1], "D") OR StartsWith([Tax Number 1], "E") and Length([Tax Number 1])!=9 THEN Replace([Tax Number 1], "A", "A0") OR Replace([Tax Number 1], "B", "B0") OR Replace([Tax Number 1], "C", "C0") OR Replace([Tax Number 1], "D", "D0") OR Replace([Tax Number 1], "E", "E0") ELSE [Tax Number 1] ENDIF

 

Could someone help me with this inconvenience? How can create a formula that recognizes the letters from A to Z and if it is less than 9 characters can add a 0 after the letter?

Thanks in advance!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
If
Regex_match([tax number1],"\u\d+") then
Left([tax number 1],1) + padleft(Substring([tax number 1],1),8,"0")
Else
Padleft(regex_replace([tax number 1],"\D",""),8,"0") + right([tax number 1])
Endif

I'm watching a movie now. I think that this will work.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi Gloasiga,

 

This will only fill pad out one missing 0 (and won't pad 2 or more zeros unlike @MarqueeCrew's superior solution). But here's how you can do it in one expression,

 

REGEX_Replace([Field1], '\b(\D?)(\d{7})(\D?)\b', '\10\2\3')

 

WorkflowWorkflow

 

Highlighted
5 - Atom

Hello @marqueecrew,

It works excellent.

 

Thank you so much, really appreciated!

 

Cheers,

Glo

Labels