Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! 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
16 - Nebula
16 - Nebula

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