cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ACraig
Level I

Keep Leading Zeros

One of the columns in my data set is Employee ID. All employee ID's have a length of 8 and begin with leading zeros. When I open the excel file that has the data JMP seems to drop those leading zeros. Is there a way to get JMP to not do that? Or does it have to do with the way that column is stored in the xlsx file?

 

I tried using the code I found on here to just add them back in but it added them for the first row and then just changed every character to 0's for every other row. Any help is greatly appreciated. 

2 REPLIES 2

Re: Keep Leading Zeros

It sounds like the numeric code is just a label or category (ID), so you could keep it as character data. Use the option in the preview pane of the Excel Import Wizard to change the default interpretation (Numeric) to Character.

txnelson
Super User

Re: Keep Leading Zeros

@Mark_Bailey  has shown the best solution.  However, if you already have one or more data tables in JMP that have the incorrect values for the ID column, below is a very simple script that will convert the values

Names Default To Here( 1 );
dt = Current Data Table();

// Change the ID column to character
dt:ID << set data type( character );

// Loop through each row adding in the missing leading zeros
For Each Row( 
	:ID = Substr( "00000000", 1, 8 - Length( :ID ) ) || :ID;
);

Just swap out the reference to :ID to the actual name of your column

Jim