Alteryx Designer Discussions

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

Padleft not working

JBO
8 - Asteroid

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.

3 REPLIES 3
MarqueeCrew
19 - Altair
19 - Altair
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.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
BenMoss
17 - Castor
17 - Castor
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
8 - Asteroid

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

Labels