topic Re: Colum formula result error in Discussions

Colum formula result error

deboraam — Fri, 15 Aug 2025 19:01:49 GMT

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

Re: Colum formula result error

jthi — Fri, 15 Aug 2025 19:17:15 GMT

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

Re: Colum formula result error

deboraam — Fri, 15 Aug 2025 20:27:51 GMT

Hi Jarmo,

Do you mean like this?

Re: Colum formula result error

txnelson — Fri, 15 Aug 2025 21:13:05 GMT

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, "-") );

Re: Colum formula result error

dlehman1 — Fri, 15 Aug 2025 21:31:53 GMT

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.

Re: Colum formula result error

jthi — Sat, 16 Aug 2025 04:57:18 GMT

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.

Re: Colum formula result error

deboraam — Sat, 16 Aug 2025 11:47:28 GMT

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.

Re: Colum formula result error

deboraam — Sat, 16 Aug 2025 12:21:32 GMT

Apparently, the issue is related to my decimal separator, which is a comma.

When I use the formula suggested by @jthi it works pretty well.

Anyway, thanks for your help!

Num(Left(:Length mm, 4), << Use Locale(0))

Re: Colum formula result error

deboraam — Sat, 16 Aug 2025 12:43:25 GMT

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!

Re: Colum formula result error

jthi — Sat, 16 Aug 2025 14:27:51 GMT

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

Re: Colum formula result error

deboraam — Sat, 16 Aug 2025 15:35:20 GMT

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

Thank you very much for your attention and support!

Cheers.