Script to do : Subtraction of multiple column

[#1]

Hi Team,

Need help to  develop one script to do subtraction of multiple column , column name and count is variable,

can change based on analysis,

Need to populate column delta with differences all the value from same row,

sample data as below

expected output:

[#2]

Hi @jinsejoseph

Can you give an example of the formula you want to use?  This sounds like something I would use the custom transformation in theScripting Tools add-in for.

[#3]

Hi , Thanks for the reply,
I am using JMP15 can you help to point to the code that i can use,
I am a beginner in JMP 

Thanks in advance 

[#4]

Expected output in delta as below,

[#5]

Now I see, in that case I would personally use the custom transformation function in theScripting Tools add-in.  I would select all of the columns you want to transform (I think data1-data10) and then select Custom Transformation from the Cols menu (available after installing the add-in) and enter these settings:

[#6]

Hi ,
I was writing a scrip for data analysis, and need this to be implement this logic in between the code only for one data table,
can you help to point to the code that i can use

Thanks in advance 

[#7 · Accepted solution]

Here is the formula that I would use.  It relies on the matrix function Loc() which return all non zero entries in a matrix.

x = :data[Row()] || :data1[Row()] || 
:data2[Row()] || :data3[Row()] || 
:data4[Row()] || :data5[Row()] ||
:data6[Row()] || :data7[Row()] || 
:data8[Row()] || :data9[Row()];
y = Loc( x );
Try( x[y[2]] - x[y[1]], . );

[#8]

Hi Jim,

Thanks for the input,

But column name is variable control and name can change based on run to run,

how to code in that situation ?

[#9]

Assuming you don't know the names, and you don't know the number of columns, the below code will create the required formula, and then create the new column with the generated formula

Names Default To Here( 1 );
dt = Current Data Table();

// Get the columns
colNames = dt << get column names( string, continuous );

// Build the formula
theExpr = "dt<<new column(\!"Delta\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || ";
y = Loc( x );
Try( x[y[2]] - x[y[1]], . )));";

// Execute the Created JSL
Eval( Parse( theExpr ) );

The sample data table I used is attached

[#10]

You could also turn it into a function if you need to use it multiple times.

Here is one example:

• Function
• Uses regex to filter down to the columns of interest
• Requires JMP16 due to Filter Each
• Uses Loc() and matrix calculations
• Creates new column delta with the values

Names Default To Here(1);

//example datatable
dt = New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 0, 0, 1, 0]),
		Set Display Width(55)
	),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([0, 2, 3, 0, 0])),
	New Column("Column 3",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([0, 3, 0, 0, 0]),
		Set Display Width(87)
	),
	New Column("Column 4", Numeric, "Continuous", Format("Best", 12), Set Values([3, 0, 4, 0, 0])),
	New Column("col", Character, "Nominal", Set Values({"a", "a", "a", "a", "a"}))
);


difference_between_min_max = Function({dt_ref, col_prefix_regex},
	{Default Local},
	//get columnn names of interest based on col_prefix
	col_names_all = dt_ref << Get Column Names("Numeric", "String");
	col_names_of_interest = Filter Each({val}, col_names_all, 
		!IsMissing(Regex(val, col_prefix_regex));
	);
	show(col_names_of_interest);
	//all values for columns of interest, transpose for calculations
	m = Transpose(dt_ref[0, col_names_of_interest]);
	//replace 0 with missing to avoid calculation with those
	m[Loc(m, 0)] = .;
	//Get max and min values and deduct them
	result = V Max(m) - V Min(m);
	//Replace missing values with 0 for final results
	result[Loc(result,.)] = 0;
	//we could return result matrix here OR we can create new column with the function
	dt_ref << New Column("delta", Numeric, Continuos, Set Values(result));
);

//example table in this example
difference_between_min_max(dt, "(Column )");

//example table by txnelson
//difference_between_min_max(Current Data Table(), "^(\D{1})$");

Could also turn it into Custom Function