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

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
Paul-Evans
9 - Comet

Are you using a string data type for the output? A numeric type will drop leading zeros.

MarqueeCrew
20 - Arcturus
20 - Arcturus

Thank you @DanielG .

 

 Hopefully we both get solution credits. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Kavya432
8 - Asteroid

Its working with the formula. But when I tried to copy to excel some how the zero's to the left side of Prod_no is disappearing(do we need to change any datatype?). 

 

Thanks all for the solutions!

 

Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?

Kavya432
8 - Asteroid

forgot to attach the data file

 

Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?

 

Kavya432_0-1581435908722.png

Kavya432
8 - Asteroid

Its not working if I use the  IsNumber([Base_Prod_no]), its not bringing any data trough.

DanielG
12 - Quasar

It has to be a string to retain the lead zeros.

 

A numeric field will drop those as they are not relevant when interpreting it as a number.

 

I tested by creating a text file with this in it: 

 

test    *   
1 0000111100000
2 11110000
3 0101010

I used Output tool to drop it was an Excel to my desktop, and it retained the string structure.

 

You just need to make sure it is a string before you push it down to its final location.

 

If you copy and paste it from the results window to Excel, it will be translated into a number.  So dont do that.  🙂

 

DanielG
12 - Quasar

use to be clear on my last post, the 1,2,3 at the front of each item is the row# not the lead digit of what I was testing...

 

Didnt realize how bad that looked when I typed and posted it initially...  

 

 

DanielG
12 - Quasar

For your second question:

 

I am sure there is a more elegant way to do it, but perhaps a formula on that column with "if contains(field, "0") then "" else y endif (with additional layers if needed)

 

Please note that is above is roughly written and will require syntax tweaking to make it accurate.

Kavya432
8 - Asteroid

No Problem, will try to output to excel.

Could you please help me to remove any alpha numeric and zeros and just leave a empty or blank field in Base_Prod_no.

 

Kavya432_0-1581437065299.png

Labels