cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Aam_jmp
Level IV

Formula to make a new column

I have a table with four columns, and I am trying to make a new column based off of the available columns. Here is my script:

dtq = current data table();
a = dtq << New Column( "E",
	Formula(
		If(
			Is Missing( :A ) & Is Missing( :B ), Abs( :C ),
			Is Missing( :A ), Abs( :C / (:B - :D) ) ,
			Is Missing( :B ), Abs( :C / (:D - :A) ),
			//If !Is Missing( :A ) & !IsMissing( :B ), 
			//then do Abs( :C / (:B - :D) ) and Abs( :C / (:D - :A) ) and choose the gretaer value out of the two. 
		)
	)
);

The first three conditions work, however the condition with values present in both A and B is something I am unable to work out. How can I make my script calculate 2 formulas and add the greater value out of these to my column in the table? Thank you for any help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Formula to make a new column

You want the Max() function.

 

JMPScreenSnapz211.png

dtq = Current Data Table();
a = dtq << New Column( "E",
	Formula(
		If(
			Is Missing( :A ) & Is Missing( :B ), Abs( :C ),
			Is Missing( :A ), Abs( :C / (:B - :D) ),
			Is Missing( :B ), Abs( :C / (:D - :A) ),
			!Is Missing( :A ) & !Is Missing( :B ),
				Max( Abs( :C / (:B - :D) ), Abs( :C / (:D - :A) ) )
		)
	)
);
-Jeff

View solution in original post

txnelson
Super User

Re: Formula to make a new column

dtq = current data table();
a = dtq << New Column( "E",
	Formula(
		If(
			Is Missing( :A ) & Is Missing( :B ), Abs( :C ),
			Is Missing( :A ), Abs( :C / (:B - :D) ) ,
			Is Missing( :B ), Abs( :C / (:D - :A) ),
			Max(Abs( :C / (:B - :D) ) ,Abs( :C / (:D - :A) ) )
		)
	)
);
Jim

View solution in original post

2 REPLIES 2
Jeff_Perkinson
Community Manager Community Manager

Re: Formula to make a new column

You want the Max() function.

 

JMPScreenSnapz211.png

dtq = Current Data Table();
a = dtq << New Column( "E",
	Formula(
		If(
			Is Missing( :A ) & Is Missing( :B ), Abs( :C ),
			Is Missing( :A ), Abs( :C / (:B - :D) ),
			Is Missing( :B ), Abs( :C / (:D - :A) ),
			!Is Missing( :A ) & !Is Missing( :B ),
				Max( Abs( :C / (:B - :D) ), Abs( :C / (:D - :A) ) )
		)
	)
);
-Jeff
txnelson
Super User

Re: Formula to make a new column

dtq = current data table();
a = dtq << New Column( "E",
	Formula(
		If(
			Is Missing( :A ) & Is Missing( :B ), Abs( :C ),
			Is Missing( :A ), Abs( :C / (:B - :D) ) ,
			Is Missing( :B ), Abs( :C / (:D - :A) ),
			Max(Abs( :C / (:B - :D) ) ,Abs( :C / (:D - :A) ) )
		)
	)
);
Jim