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.
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
cbaril
Level III

How to apply a script on subsets of a datatable

Hello,

 

In my datatable I have 6 data subsets (distinguished one another by Column X where each subset has a different letter "a", "b", "c", "d", "e" and "f"). How could I apply a script to all 6 subsets independently and get the 6 results of my script in one column?  

 

Thank you in advance for your support!

 

Best

Claire  

 

------

Script currently applied to entire datatable:

 

dt = Current Data Table();

dt << New Column("sumtempj",
	Numeric,
	Continuous,
	<<Set Each Value(
		As Constant(
			ts = :"Rounded TempJ" << get values;
			f_row = Min(Loc(ts, 121));
			t_idx = Index(f_row, f_row + 359);
			sumtempj = Sum(ts[t_idx]) - (360 * 121);
		);
		If(
			Row() == f_row, sumtempj,
			, .
		);
	)
);

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to apply a script on subsets of a datatable

Just based on the script you have here, I would add index column, create subsets based on the unique letters, run formula on each of those and then update data back to original table

-Jarmo

View solution in original post

4 REPLIES 4
jthi
Super User

Re: How to apply a script on subsets of a datatable

Just based on the script you have here, I would add index column, create subsets based on the unique letters, run formula on each of those and then update data back to original table

-Jarmo
cbaril
Level III

Re: How to apply a script on subsets of a datatable

Hi Jarmo,

Thanks for the tip!

 

I'm only a beginner in JSL and now have this script (see below). The result column "sumtempj" appears in each sub datatable produced but there is no result in the column. Can you see the mistake?

 

Best

Claire

 

Script:

dt = Current Data Table();

// Perform the subsets
listDT = dt << Subset(
	By( :BBI and SteriRun ),
	All rows,
	Selected columns only( 0 ),
	columns( :RoundedTempJ, :BBI and SteriRun, :Sterilisation duration )
);

// Loop across all new data tables and run sumtempj
For( i = 1, i <= N Items( listDT ), i++,
	listDT[i] << New Column( "sumtempj",
		Numeric,
		Continuous,
		<<Set Each Value(

			As Constant(
				ts = :Rounded TempJ << get values;
				f_row = Min( Loc( ts, 121 ) );
				t_idx = Index( f_row, f_row + 359 );
				sumtempj = Sum( ts[t_idx] ) - (360 * 121);
			);
			If(
				Row() == f_row, sumtempj,
				, .
			);
		)
	)
);
jthi
Super User

Re: How to apply a script on subsets of a datatable

There might be other ways of doing this, but as I don't know your data I cannot really suggest anything. Here is one solution which uses subsets which could work

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

// add indexing to original data
dt << New Column("IDX", Numeric, Continuous, << Set Each Value(Row()));
// add data column to table as empty
new_col = dt << New Column("sumtempj", Numeric, Continuous);
col_name = new_col << get name;
// get unique groups to uniq_vals
Summarize(dt, uniq_vals_str = by(:age));

// in this case we have to convert uniq_vals back to numeric values BUT it isn't always necessary
// (summarize changes them to strings)
uniq_vals = Transform Each({uniq_val_str}, uniq_vals_str, num(uniq_val_str));

// loop over those unique values 
For Each({uniq_val}, uniq_vals,
	// create subset
	uniq_val_rows = dt << Get Rows Where(:age == uniq_val);
	dt_temp = dt << Subset(rows(uniq_val_rows), output table(char(uniq_val)), invisible);
	
	// perform calculation
	Column(dt_temp, col_name) << Set Each Value(
		Col Max(:height);
	);
	// update values
	dt << Update(
		With(dt_temp),
		Match Columns(:IDX = :IDX)
	);
	// wait is here for demo purposes
	wait(1);
	close(dt_temp, no save);
);

dt << Delete Columns("idx"); 
-Jarmo
cbaril
Level III

Re: How to apply a script on subsets of a datatable

This is a bit too advanced for me, but thank you for the inputs! I managed to find a way to get the result without too much manual handling and for now it's good enough.

 

Best

Claire