3 Replies Latest reply on Jun 14, 2016 9:25 AM by Joshua Milligan

    Period on end of value when zero padding using STR/IF functions

    Jeremy Poole

      I have a use case this week where I'm receiving vendor UPC's of varying lengths, but they need to be space padded to a consistent 13-digit length. So I wrote an IF statement based upon the fields I receive to convert them to this 'proper' format. Instead of the correct padding, i'm getting 1 too few leading 0's and getting a period on the end instead.

       

      My Calculation for Vendor UPC is as follows:

      if len(str([Ven UPC]))=12 then "0"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=11 then "00"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=10 then "000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=9 then "0000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=8 then "00000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=7 then "000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=6 then "0000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=5 then "00000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=4 then "000000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=3 then "0000000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=2 then "00000000000"+Str([Ven UPC])

      elseif len(str([Ven UPC]))=1 then "000000000000"+Str([Ven UPC])

      ELSE str([Ven UPC])

      END

       

      I've attached a screenshot of what i'm seeing (where Calculation1 corresponds to the LEN of the initial field Ven UPC). Has anyone seen this behavior...or more realistically, am I missing something simple? Using Desktop 9.01(corp standard) FYI. Thanks in advance!