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

Multiple IF and Startswith assessment

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

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
PhilipMannering
16 - Nebula
16 - Nebula

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

 

Gloaisiga
5 - Atom

Hello @marqueecrew,

It works excellent.

 

Thank you so much, really appreciated!

 

Cheers,

Glo

Labels