Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Level VII

## Why can't this formula do the whole column calculation?

The requirement is that if the number of rows is less than 25 and the rightmost number in the first column is 0, extract the 12 Numbers in the first column, otherwise return 0

If I write the formula this way, it will appear that when the rightmost number in the first column is 1, there will be no result, not even the calculation below.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Level VII

## Re: Why can't this formula do the whole column calculation?

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",
Substr( :Label, 14, 12 ),
0
)

Highlighted
Staff

## Re: Why can't this formula do the whole column calculation?

There may be some other issues, but the first thing is that your formula is mixing data types.

The Label column is character. Your formula in the "if" clause is using a substring, which is a character function. That is good.

Your "else" clause just has a zero (0), which is numeric. Put that in double quotes (like you did in the "if"  statement) to get rid of the JMP alert that you captured.

Alternatively, you could put a Num() around the SubStr(Label,14,12) and make sure Column3 is numeric.

Dan Obermiller
3 REPLIES 3
Highlighted
Level VII

## Re: Why can't this formula do the whole column calculation?

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",
Substr( :Label, 14, 12 ),
0
)

Highlighted
Staff

## Re: Why can't this formula do the whole column calculation?

There may be some other issues, but the first thing is that your formula is mixing data types.

The Label column is character. Your formula in the "if" clause is using a substring, which is a character function. That is good.

Your "else" clause just has a zero (0), which is numeric. Put that in double quotes (like you did in the "if"  statement) to get rid of the JMP alert that you captured.

Alternatively, you could put a Num() around the SubStr(Label,14,12) and make sure Column3 is numeric.

Dan Obermiller
Highlighted
Level VII

## Re: Why can't this formula do the whole column calculation?

Thank you!
OK

If( Row() < 25 & Substr( :Label, 25, 1 ) == "0",
Substr( :Label, 14, 12 ),
"0"
)
Article Labels

There are no labels assigned to this post.