Padleft not working

[JBO]

I have numbers of varying length in a field. 

All values need to be 7 characters long and need zeros appended to left of existing number. So 41449 should be 0041449.

I have tried the following (based on other posts) and for some reason it won't add the zeros.

• First attempt:
  • Using formula tool created a new field called "code_length" to determine how many zeros are needed (set data type to double):  7 - Length([Field-1])
  • Using formula tool added this formula:  If Length([Field-1) = 7 then [Field-1] else PadLeft([Field-1], [code_length], "0") endif
  • Confirmed format of field in Excel source was General. I also tried Text. Neither changed the result.
  • Confirmed field is importing as string in Alteryx. Tried each string type with no success.
• Second attempt:
  
  • Replaced the conditional statement with the following (to test whether PadLeft working at all):  PadLeft([Field-1], 2, "0")
  • This did not work. 
• Third attempt:
  • Tried creating a new Excel source to clear out any formatting I might be missing.
  • This did not work.

I am not sure what I am doing wrong. Can anyone help?

Thanks.

[MarqueeCrew · Accepted solution]

Padleft(ToString([field]),7,"0")

Try it. You'll like it.

Yes! You have to store this as a string. Storing as an integer will erase the leading zeros.

[BenMoss]

And make sure you set the field type to be strong, as you know the length, you can choose just string and put the length as 7.

If you use a double you'll immediately strip the 0's you just prefixed your value with!

[JBO]

Ack! @MarqueeCrew @BenMoss I feel foolish. Of course I over-complicated it. Thank you!

[Hiblet]

Also: PadLeft needs the numeric value to be the length of the whole final string.  So, if you are putting 2 chars on at the start, the length needs to be the current length plus 2, ie "2+Length([Field])".  Otherwise, PadLeft() does nothing and seems to not be working.