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

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.

4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
BenMoss
ACE Emeritus
ACE Emeritus
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!

Hiblet
10 - Fireball

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.

 

Labels