We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Length of fields

Kavya432
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

 

 

18 REPLIES 18
fmvizcaino
17 - Castor
17 - Castor

Hi @Kavya432 ,

 

Attached is an example showing how to do it. 

Let me know if this works for you.

 

BEst,

Fernando Vizcaino

DanielG
12 - Quasar

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?

Paul-Evans
9 - Comet

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
ChrisTX
15 - Aurora

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

Kavya432
8 - Asteroid

THIS IS ONLY IF LENGTH OF PROD_NO <= 5.

 

THIS FORMULA IS APPLYING TO ALL PROD_NO

Kavya432
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

DanielG
12 - Quasar

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

DanielG
12 - Quasar

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