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
WendyLou315
Level III

Formula to toggle value (1 or 0) in new column based on change by row in existing column

I have a question on creating a formula to do something in JSL that I do very easily in Excel. 

 

If I were to have Column A and I want to generate Column B, I would put a zero in B1 then add the formula: 

=IF(A2<>A1,IF(B1=0,1,0),B1)

into B2 then copy down the column.  Is there a formula to do that in JMP?  I have tried Lag() but it doesn't give me the results I'm looking for.

JSL_Question.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

Try this formula:

 

Capture.PNG

Learn it once, use it forever!

View solution in original post

Highlighted

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

The formula is exactly the same as what you would put into Excel. The differences are that you need specify the first row in the formula and you need to know exactly what the Excel autofill is doing (which is not always straight-forward).

 

Interactively, it would look like this:

JMP Formula.PNG

 

The JSL code would look like this:

If(
	Row() == 1, 0,
	:A[Row()] != Lag( :A, 1 ), If( Lag( :B, 1 ) == 0, 1, 0 ),
	Lag( :B, 1 )
)

 

 

Or just use Mark's formula which looks simpler!

Dan Obermiller

View solution in original post

3 REPLIES 3
Highlighted

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

Try this formula:

 

Capture.PNG

Learn it once, use it forever!

View solution in original post

Highlighted

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

The formula is exactly the same as what you would put into Excel. The differences are that you need specify the first row in the formula and you need to know exactly what the Excel autofill is doing (which is not always straight-forward).

 

Interactively, it would look like this:

JMP Formula.PNG

 

The JSL code would look like this:

If(
	Row() == 1, 0,
	:A[Row()] != Lag( :A, 1 ), If( Lag( :B, 1 ) == 0, 1, 0 ),
	Lag( :B, 1 )
)

 

 

Or just use Mark's formula which looks simpler!

Dan Obermiller

View solution in original post

Highlighted
WendyLou315
Level III

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

@Dan_Obermiller, You are correct, @markbailey had a simpler option but both work perfectly!  THANKS so much!!!

Article Labels

    There are no labels assigned to this post.