Colum formula result error

[#1]

Hello everybody!

I need help to solve a problem with a colum formula result error that I am having with my JMP.

It is important to mention that this error apparently happens only with my JMP. When a collegue in another company uses the same formula in their JMP it works perfectly, and surprisingly, when they send me the JMP Data Table with this formula it works pretty well im my JMP. So the problem is when I start a JMP Data Table from scratch.

 

I think that the problem is in the settings but could not find it. Could anyone help me with that?

Thanks in advance for your attention!

Cheers,

Débora Mattos

[#2]

Could it be your decimal separator? You could try if << Use Locale(0/1) would help, Scripting Guide > Types of Data > Numbers and Strings. 

[#3]

Hi Jarmo,

Do you mean like this? 

[#4 · Accepted solution]

Num(Left(:Column, 4), << Use Locale(0))

Also, in general what are is your decimal separator? If it is comma, it could be causing the issue. You can also first try to "solve" this in JMP Script Editor to avoid all possible "issues" JMP table could be causing

Show(Num(Left("0.01 - 0.02", 4), << Use Locale(0)));
Show(Num(Left("0,01 - 0,02", 4), << Use Locale(0)));

Show(Num(Left("0.01 - 0.02", 4), << Use Locale(1)));
Show(Num(Left("0,01 - 0,02", 4), << Use Locale(1)));

Num() will return missing value if it isn't able to convert the string to a number.

[#5]

Yes, my decimal separator is a comma.

The problem was only solved when using the Left function. Do you know why the Substr function didn't work?

I will post the outcomes below from other formulas I tried, just to document what worked and didn't worked for me.

I hope this helps others as well.

[#6]

I would think Substr(x, 1, 4) works exactly the same as Left(x, 4) so my guess is that in your formula, with Substr, you are just missing comma after Substr

Num(Substr("0.01 - 0.02", 1, 4), << Use Locale(0))

[#7]

You're right... including comma worked as well.

Thank you very much for your attention and support!

Cheers.

[#8]

1.  Try displaying Length MM using a Hex format to see if there are any hidden special characters in your data.

2.  I normally use the Word() function rather than Substr() in cases like this.

num( trim( word( 1, :length mm, "-") );

[#9]

I tried to change the display in the "Length mm" column, as you suggested, but I couldn't find the Hex format among the format options. Anyway, I tried your formula to see if it worked, but it didn't.

It ended up working only with the formula suggested by @jthi.

Even so, thanks for your support!

[#10]

When I do this, it works, but the column type changes to nominal.  I thought the Substr function produces a nominal result, not a continuous one.  It changes the data type when I click ok, but the preview shows the correct result, not a blank result like yours.