topic Re: Replacing Values with Empty Value in Discussions

Replacing Values with Empty Value

rfeick — Fri, 07 Jul 2017 14:28:58 GMT

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?

Re: Replacing Values with Empty Value

Mark_Bailey — Fri, 07 Jul 2017 14:42:40 GMT

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?

Re: Replacing Values with Empty Value

rfeick — Fri, 07 Jul 2017 14:52:17 GMT

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.

Re: Replacing Values with Empty Value

rfeick — Fri, 07 Jul 2017 14:53:37 GMT

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(

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( "-" )

)

);

Re: Replacing Values with Empty Value

rfeick — Fri, 07 Jul 2017 15:02:45 GMT

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