Subtract between multiple columns

kuanaunwei — Thu, 22 Feb 2024 03:50:35 GMT

dt = current data table();
For ( i=0 , i<8, i++,
dt << 	new column( "RESULT", 
		Numeric,
		"Continuous",
		For (x=1, x<50, x++,
			Formula(Column(3+i)[x]-Column(2+i)[x]),
		));
);

I would like to subtract data between multiple columns, i have tried the script above but its not working. Is there a more effective way to do it?

Expected result:

result = data2 - data1

result1 = data4 - data3

result2 = data6 - data5

Item	data1	data2	data3	data4	data5	data6	data7	data8	data9	data10	data11	data12	data13	data14	data15	data16
A1	0.51	0.53	0.48	0.53	0.49	0.60	0.56	0.69	0.62	0.77	0.55	0.82	0.58	0.85	0.61	0.88
A2	0.49	0.51	0.48	0.53	0.48	0.60	0.57	0.70	0.61	0.78	0.56	0.83	0.59	0.86	0.64	0.90
A3	0.50	0.52	0.46	0.52	0.49	0.60	0.56	0.69	0.62	0.77	0.54	0.81	0.58	0.84	0.62	0.88
A4	0.47	0.50	0.48	0.53	0.48	0.60	0.57	0.70	0.63	0.78	0.56	0.83	0.60	0.86	0.63	0.89
A5	0.49	0.50	0.51	0.52	0.59	0.60	0.68	0.70	0.74	0.78	0.80	0.84	0.82	0.87	0.85	0.90
A6	0.47	0.50	0.49	0.54	0.48	0.61	0.55	0.70	0.62	0.78	0.59	0.85	0.64	0.89	0.69	0.94
A7	0.48	0.51	0.48	0.53	0.48	0.60	0.58	0.71	0.65	0.80	0.60	0.85	0.63	0.88	0.65	0.90
A8	0.51	0.53	0.48	0.53	0.49	0.60	0.56	0.69	0.62	0.78	0.54	0.82	0.58	0.85	0.63	0.89
A9	0.50	0.52	0.48	0.53	0.48	0.59	0.49	0.69	0.61	0.77	0.55	0.82	0.60	0.86	0.63	0.89
A10	0.51	0.53	0.49	0.54	0.49	0.60	0.54	0.69	0.61	0.77	0.54	0.82	0.57	0.85	0.62	0.89
A11	0.52	0.53	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.81	0.86	0.84	0.90
A12	0.51	0.52	0.53	0.54	0.61	0.62	0.68	0.70	0.76	0.79	0.80	0.84	0.83	0.88	0.86	0.92
A13	0.48	0.51	0.48	0.53	0.48	0.60	0.57	0.70	0.64	0.79	0.56	0.83	0.59	0.86	0.64	0.90
A14	0.47	0.50	0.47	0.53	0.49	0.60	0.57	0.70	0.63	0.78	0.56	0.83	0.59	0.86	0.63	0.90
A15	0.47	0.50	0.47	0.52	0.47	0.59	0.56	0.69	0.63	0.78	0.56	0.83	0.59	0.86	0.64	0.90
A16	0.51	0.53	0.48	0.53	0.48	0.60	0.57	0.70	0.62	0.77	0.54	0.82	0.58	0.85	0.62	0.89
A17	0.48	0.51	0.46	0.51	0.47	0.59	0.56	0.69	0.62	0.77	0.55	0.82	0.60	0.86	0.63	0.89
A18	0.48	0.51	0.47	0.52	0.48	0.60	0.56	0.70	0.61	0.77	0.56	0.83	0.61	0.87	0.64	0.90
A19	0.52	0.54	0.49	0.54	0.49	0.60	0.57	0.70	0.63	0.78	0.56	0.83	0.60	0.86	0.64	0.90
A20	0.51	0.52	0.52	0.53	0.58	0.60	0.67	0.70	0.75	0.79	0.80	0.84	0.83	0.88	0.86	0.91
A21	0.53	0.53	0.53	0.54	0.60	0.61	0.69	0.71	0.75	0.79	0.80	0.84	0.82	0.87	0.85	0.90
A22	0.53	0.53	0.53	0.54	0.60	0.61	0.68	0.70	0.75	0.79	0.80	0.84	0.82	0.87	0.85	0.91
A23	0.48	0.51	0.48	0.53	0.50	0.61	0.56	0.69	0.62	0.77	0.53	0.82	0.59	0.86	0.62	0.89
A24	0.48	0.51	0.47	0.53	0.48	0.60	0.59	0.72	0.65	0.80	0.60	0.85	0.63	0.88	0.64	0.90
A25	0.48	0.51	0.47	0.52	0.48	0.60	0.57	0.70	0.62	0.77	0.54	0.82	0.59	0.86	0.62	0.89
A26	0.49	0.51	0.47	0.52	0.49	0.60	0.57	0.70	0.64	0.79	0.57	0.84	0.59	0.86	0.64	0.90
A27	0.51	0.52	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.82	0.86	0.85	0.89
A28	0.51	0.52	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.81	0.86	0.85	0.90
A29	0.52	0.54	0.49	0.54	0.51	0.61	0.58	0.71	0.65	0.80	0.58	0.84	0.63	0.88	0.68	0.93
A30	0.48	0.51	0.47	0.52	0.39	0.59	0.57	0.70	0.64	0.79	0.58	0.84	0.60	0.86	0.63	0.89
A31	0.49	0.52	0.46	0.52	0.48	0.60	0.57	0.70	0.63	0.78	0.57	0.83	0.61	0.87	0.64	0.90
A32	0.51	0.53	0.48	0.53	0.50	0.61	0.57	0.70	0.63	0.78	0.56	0.83	0.60	0.86	0.63	0.89
A33	0.50	0.52	0.48	0.53	0.50	0.61	0.57	0.70	0.63	0.78	0.56	0.83	0.60	0.87	0.65	0.91
A34	0.48	0.51	0.48	0.53	0.49	0.60	0.58	0.71	0.64	0.79	0.56	0.84	0.59	0.86	0.62	0.89
A35	0.47	0.50	0.47	0.52	0.47	0.59	0.55	0.69	0.61	0.77	0.55	0.82	0.60	0.86	0.65	0.90
A36	0.49	0.52	0.47	0.53	0.48	0.59	0.56	0.69	0.61	0.77	0.55	0.82	0.60	0.86	0.63	0.89
A37	0.48	0.51	0.48	0.53	0.48	0.60	0.58	0.71	0.64	0.79	0.57	0.84	0.61	0.87	0.65	0.91
A38	0.50	0.52	0.48	0.53	0.48	0.60	0.57	0.70	0.63	0.78	0.59	0.85	0.61	0.88	0.65	0.91
A39	0.53	0.53	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.83	0.87	0.85	0.90
A40	0.53	0.53	0.53	0.54	0.60	0.61	0.68	0.70	0.75	0.79	0.80	0.84	0.83	0.87	0.85	0.91
A41	0.51	0.53	0.48	0.53	0.49	0.60	0.54	0.69	0.61	0.77	0.55	0.82	0.60	0.86	0.64	0.90
A42	0.59	0.50	0.47	0.52	0.49	0.60	0.57	0.70	0.64	0.79	0.56	0.83	0.61	0.87	0.64	0.90
A43	0.51	0.52	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.82	0.86	0.85	0.90
A44	0.51	0.52	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.81	0.85	0.84	0.89
A45	0.51	0.52	0.53	0.54	0.59	0.60	0.69	0.71	0.76	0.80	0.81	0.85	0.83	0.88	0.86	0.92
A46	0.49	0.51	0.48	0.53	0.48	0.60	0.57	0.70	0.62	0.78	0.56	0.83	0.61	0.87	0.66	0.91
A47	0.49	0.52	0.48	0.53	0.47	0.59	0.54	0.69	0.61	0.76	0.52	0.81	0.56	0.84	0.61	0.88
A48	0.47	0.50	0.47	0.52	0.47	0.59	0.57	0.70	0.63	0.78	0.56	0.83	0.59	0.86	0.63	0.90
A49	0.50	0.51	0.52	0.53	0.59	0.60	0.68	0.70	0.74	0.78	0.79	0.83	0.82	0.87	0.85	0.90

Re: Subtract between multiple columns

WebDesignesCrow — Thu, 22 Feb 2024 04:38:02 GMT

Hi @kuanaunwei ,

For me, the effective way to learn the correct script is to use "Workflow" (I'm using JMP 17).

Below is the how I would do it;

I use button-click to get the result manually - then, check the code at "Workflow". Add it in my script

From your JSL script above, I'm not sure, whether you just want to have a simple formula column or formula column with conditions (IF/Else)?

Re: Subtract between multiple columns

kuanaunwei — Thu, 22 Feb 2024 04:46:28 GMT

The column names will be different, and the size of the data will also be different. So workflow might not be a best solution for my case. Thanks for the recommendation :)

Re: Subtract between multiple columns

jthi — Thu, 22 Feb 2024 06:27:10 GMT

This might give some additional ideas

Names Default To Here(1);

dt = Open("$DOWNLOADS/724991.jmp");


cont_cols = dt << Get Column Names(Continuous, "String");
first_cols = cont_cols[2::N Items(cont_cols)::2];
second_cols = cont_cols[1::N Items(cont_cols)::2];

If(N Items(first_cols) != N Items(second_cols),
	Throw("column mismatch");
);

For Each({{col1, col2}}, Across(first_cols, second_cols),
	Eval(EvalExpr(
		new_col = dt << New Column("Result", Numeric, Continuous, Formula(
			Expr(Name Expr(AsColumn(dt, col1))) - Expr(Name Expr(AsColumn(dt, col2)))
		));		
	));
	dt << Run Formulas;
	new_col << Suppress Eval(1);
	
);

If you just care about the results, other option is to stack your data, calculate the result to stacked table, split it (might have to add new column for proper result column naming) and join it back

and then JMP is able to create some sort of a script for you

Re: Subtract between multiple columns

kuanaunwei — Tue, 27 Feb 2024 07:50:09 GMT

Sorry for the late response, I could not log into my JMP account for some reason. Also, thanks for the idea given on the scripting side. That helped

topic Re: Subtract between multiple columns in Discussions

Subtract between multiple columns

Re: Subtract between multiple columns

Re: Subtract between multiple columns

Re: Subtract between multiple columns

Re: Subtract between multiple columns