Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Length of fields

Highlighted
8 - Asteroid

Hi Team,

 

I am looking for a logic for the below requirement I have.

 

I have a field called prod_no

 

If length of pros_no = 5 then I should add '000' to the left to make it 8 digit number

for example if prod_no = 12345 then result should be like 12345000

 

If length of prod_no = 4 then I should add 000 to the left and 0 to the right

for example if prod_no = 1234 then result should be like 01234000

 

If length of prod_no = 3 then I should add 000 to the left and 00 to the right

for example if prod_no = 123 then result should be like 00123000

 

If length of prod_no = 2 then I should add 000 to the left and 000 to the right

for example if prod_no = 12 then result should be like 00012000

 

If length of prod_no = 1 then I should add 000 to the left and 0000 to the right

for example if prod_no = 123 then result should be like 00001000

 

Appreciate if you can give formula for this problem.

 

Other questions is for same field prod_no I have Characters and alpha numeric characters in it. How I can filter those out.

 

for example prod_no = 0nanana000

                                     H290478

 

Thanks

Kavya

 

 

Highlighted
Alteryx Certified Partner

Hi @Kavya432 ,

 

Attached is an example showing how to do it. 

Let me know if this works for you.

 

BEst,

Fernando Vizcaino

Highlighted
8 - Asteroid

If Length([Field]) = 5 then [Field]+"000" elseif

Length([Field]) = 4 then "0"+Field+"000" elseif

Length(Field]) = 3 then "00"+Field+000" elseif

Length(Field)= 2 then "000"+FIeld+"000" elseif

Length(Field)=1 then "0000"+Field+"000"

else

Field

endif

 

As for the character piece, can you use Data Cleanse to remove characters you dont want in that field?

Highlighted
8 - Asteroid

PadRight(PadLeft([prod_no],5,"0"),8,"0")

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@Kavya432,

 

I hear lots of answers coming in.  Are they all solutions?  Here's my K.I.S.S. solution:

 

PadLeft(ToString([prod_no])+"000", 8, "0")

Cheers,


Mark 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
12 - Quasar

For your last question....I have Characters and alpha numeric characters in it. How I can filter those out.

 

Try a filter with the IsNumber function

Highlighted
8 - Asteroid

THIS IS ONLY IF LENGTH OF PROD_NO <= 5.

 

THIS FORMULA IS APPLYING TO ALL PROD_NO

Highlighted
8 - Asteroid

I tried to use this formula

 

if Length([Prod_no_new])<=5 Then PadRight(PadLeft([Prod_no_new],5,"0"),8,"0") Else [Prod_no_new] endif

 

but its only padding to the right, not padding to the left. I just quickly validated data. Could someone help to correct the formula

 

Kavya432_0-1581378315065.png

Highlighted
8 - Asteroid

DId you try mine?  That should work.  Not as slick as the others, perhaps, but functionally it should get you what you want.

Highlighted
8 - Asteroid

Plus @MarqueeCrew has the correct formula.  No need to try and put that into an if statement.

 

Just run it as a standalone formula on the field and you will get the results you want.

 

Much simpler than my wannabe Excel formula.  haha.

Labels