273153_en-US

liliconcom

JMP turns character into date

I found some odd behaviour in JMP. I don't know why it happens and how to fix it.

Here's what I did:

- set data/modeling type of Column 1 to Character/Nominal

- enter the value 5.0

- create a second column

- use the formula Num(Column 1) in this second column

- JMP turns this 5.0 into a weird date-type of thing, in this case 3039984000, instead of just 5 or 5.0

 

Also see attached file.

I did not ask for a date format and it's also not showing any kind of date format in the Column Info properties.

 

Thanks for helping out.

Data TableOtherRe: JMP turns character into dateIt does, thanks!Re: JMP turns character into date

Hi,

 

I really don't know why JMP is interpreting that as a date.  In the meantime, does this formula help?:

 

Num( Substitute( :Column1, ".", "," ) )

 

Re: JMP turns character into date

This solves the issue, however the data in Column 1 is actually being pulled from a database so I would like to leave that column as is and convert it into a numeric using a Formula. Is there way to convert the dot into a comma using the Formula? That would solve it I guess.

 

Still it's strange why JMP really wants to turn it into a date, there doesn't seem to be any requirement defined on the columns.

Re: JMP turns character into date

Yes that helps.

Problem though is that the 5.0 in my example is actually being pulled from a database in this format.

Would there be an easy way to change the dot to a comma using a Formula, prior to converting it into a numeric value?

Re: JMP turns character into date

Hi,

 

I believe this has to do with using a period or comma as the decimal indicator.  Please see the image below.  My usual decimal indicator is a comma.  In the first row I use a comma and the number converts correctly, but in the second for I use a period and get the same result as you did.  If you highlight the column in question and go to "Edit-->Search-->Find" you can use the "replace all" feature to put the values in Column 1 to the correct format.  Let us know if this resolves the issue.

 

 

Re: JMP turns character into date

JMP 15.0.0 on Microsoft Windows 10 Enterprise version 10.0.17134.

Re: JMP turns character into dateI was not able to replicate this behavior. What version of JMP are you using, and what operating system?