Subscribe Bookmark RSS Feed

Replacing Values with Empty Value

rfeick

Community Trekker

Joined:

Sep 30, 2016

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
markbailey

Staff

Joined:

Jun 23, 2011

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?

Learn it once, use it forever!
rfeick

Community Trekker

Joined:

Sep 30, 2016

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

Community Trekker

Joined:

Sep 30, 2016

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

Community Trekker

Joined:

Sep 30, 2016

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