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

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

jinsejoseph — Sat, 10 Jun 2023 23:39:25 GMT

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:

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

markschwab — Tue, 26 Oct 2021 15:10:33 GMT

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

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

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

jinsejoseph — Tue, 26 Oct 2021 15:21:33 GMT

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

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

ih — Tue, 26 Oct 2021 21:20:29 GMT

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] ) ), 
			.
		) )
	)
) );