How to pad a number with leading zeros

Options
Clément_Stenac
Clément_Stenac Dataiker, Dataiku DSS Core Designer, Registered Posts: 753 Dataiker

A common requirement when you have a column of numbers is to format all numbers so that they have the same length, adding leading zeros if needed.

This can be done in the DSS preparation recipe using a Formula.

The formula function to use is format. For example, to ensure that all values of the column mycolumn are padded to have a length of 11, including leading zeros, use: format("%011d", mycolumn)

Clément_Stenac_0-1585160183862.png

It is useful to remember the usual formula rules to refer the values of columns, as described in the DSS reference documentation.

For example, to do the same in a column named "my column" (note the space), you would use instead format("%011d", numval("my column"))</code></p><p><code>format is actually a very powerful function that uses Java string formatting capabilities. See this Java reference for more details.

Comments

  • You
    You Registered Posts: 1 ✭✭✭
    Options

    easy way to solve headache issue if you do not see artical

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    edited July 17
    Options

    @Clément_Stenac

    This will be super helpful for USA postal codes/zip codes that have been turned to integers and truncated somewhere in the data process prior to getting into DSS.

    Getting leading 0s back in place for the northeastern United States can be sort of a pain. This should help a lot.

    I ended up with a formula like this for US 5 Digit Postal Codes with Leading "0"s :

    if(isBlank(postal_zip5),'',format("%05d",postal_zip5))

Setup Info
    Tags
      Help me…