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
rfeick
Level IV

Replacing Values with Empty Value

I have a script that is based on an imported excel table. I do not have the ability to change anything in the Excel table and have to do all data manipulation and analysis through JMP. The point of the script is to calculate days between dates entered to track project phases. Some of the dates are importing incorrectly as 31Dec1899 based on the way the formula is set up in the Excel sheet and there are also times when a dash is used to denote an not applicable value. I would like to modify these values to be empty/blank with the "." as if nothing had been entered. Is there  a way to do this? I have tried using match, substitute, and an if statement, but nothing seems to work. Is there a way to do this?

4 REPLIES 4

Re: Replacing Values with Empty Value

How does this variable come in to a data table? What does the column look like where you have valid dates and this invalid date?

rfeick
Level IV

Re: Replacing Values with Empty Value

When the date is populating as 31Dec1899 it is coming from blank cells in the corresponding column in the Excel sheet. The ones with the dashes are entered as dashes in the Excel sheet.

rfeick
Level IV

Re: Replacing Values with Empty Value

Here's the script section I'm using to import the file.

NS = Open("File Path Here",

Worksheets( "PEN Status Log" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(

1,

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 3 ),

Data Starts on Row( 4 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Replicated Spanned Headers( 0 ),

Suppress Hidden Rows( 0 ),

Suppress Hidden Columns( 0 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 ),

Multiple Series Stack( 0 ),

Import Cell Colors( 1 ),

Limit Column Detect( 0 ),

Column Separator String( "-" )

)

);

 

rfeick
Level IV

Re: Replacing Values with Empty Value

And the column it's importing to is

Data Type: Numeric

Modeling Type: Continuous

Format: ddMonyyy

 

The dates from the Excel sheet are entered as: dd-Mon-yyyy