cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
jinsejoseph
Level III

JMP script to substract column value, if nth column have no data i need to take N-1 column value

Hi Team,

 

Please help to suggest some solution for below issue

Need to create a script that can work in both situation,

column 8 = column 7-column6 

but if column 6 is having empty cell then , column 8 = column 7-column 5

 

Note: column name is variable control

Input:

jinsejoseph_1-1635257326476.png

 

 

 

3 REPLIES 3
markschwab
Level IV

Re: JMP script to substract column value, if nth column have no data i need to take N-1 column value

You could use the IsMissing() function with If statements:

 

If( Is Missing( :Column 6 ),
	:Column 7 - :Column 5,
	:Column 7 - :Column 6
)

 

jinsejoseph
Level III

Re: JMP script to substract column value, if nth column have no data i need to take N-1 column value

Hi,

The column name is variable control, can you help me to update able logic with variable control logic ,

 

Column names are from an array , were i need to get the last 2 column name 

ih
Super User (Alumni) ih
Super User (Alumni)

Re: JMP script to substract column value, if nth column have no data i need to take N-1 column value

What do you mean by 'variable control'?  Do you mean a column name is contained in a variable called control?

 

Here I am just guessing at what you are looking for, maybe something here helps get you started?

Names default to here(1);

//Open some sample data
dt = Open( "$Sample_data/big class.jmp" );

//Delete some values
for(i=1, i<= 20, i++,
	Column( dt, random integer(4,5))[random integer(40)] = .
);

//Get column names in a list/variable
cols = dt << Get Column Names( "String" );

//Create a new column referencing the last two columns
c = n items(cols);
Eval( Eval Expr(
	New Column( "New Value", Numeric, "Continuous", Format( "Best", 12 ),
		Formula( If( 
			!Is Missing( As Column( Expr( cols[c] )) ), 
			As Column( Expr( cols[c] ) ), 
			!Is Missing( As Column( Expr( cols[c-1] ) ) ), 
			As Column( Expr( cols[c-1] ) ), 
			.
		) )
	)
) );