Deleting cell values based on condition

fsanjuan

Hi!

 

I'm currently working on a project using JMP/JSL and on my data, I need to delete or clear values from multiple cells based on a condition. See sample raw data and required output below.

 

Raw data: 

Test1	P/F 1	Test2	P/F 2	Test3	P/F 3	Test4	P/F 4	Test5	P/F 5
1	P	2	P	4	P	9	F	3	P
5	P	8	F	4	P	6	F	2	P
7	P	1	P	6	F	5	F	6	F
4	P	1	P	9	F	9	F	9	F
6	F	4	P	10	F	10	F	8	F

 

Required Output:

Test1	P/F 1	Test2	P/F 2	Test3	P/F 3	Test4	P/F 4	Test5	P/F 5
1	P	2	P	4	P	9	F	*	*
5	P	8	F	*	*	*	*	*	*
7	P	1	P	6	F	*	*	*	*
4	P	1	P	9	F	*	*	*	*
6	F	*	*	*	*	*	*	*	*

 

What is required here is to clear all following cells once 1st F (failure) is triggered.

I'm working on large data and I believe this can only be done through scripting.

Tried looping functions but none so far works. I'm really not sure if this is even possible on JSL.

 

Thank you in advance for your help!

vince_faller

See if this works for you.  

 

Names default to here(1);
dt = New Table( "Example",
	Add Rows( 5 ),
	New Column( "Test1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1, 5, 7, 4, 6] )
	),
	New Column( "P/F 1",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "P", "P", "F"} )
	),
	New Column( "Test2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 8, 1, 1, 4] )
	),
	New Column( "P/F 2",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "F", "P", "P", "P"} )
	),
	New Column( "Test3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [4, 4, 6, 9, 10] )
	),
	New Column( "P/F 3",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "F", "F", "F"} )
	),
	New Column( "Test4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [9, 6, 5, 9, 10] )
	),
	New Column( "P/F 4",
		Character( 16 ),
		"Nominal",
		Set Values( {"F", "F", "F", "F", "F"} )
	),
	New Column( "Test5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [3, 2, 6, 9, 8] )
	),
	New Column( "P/F 5",
		Character( 16 ),
		"Nominal",
		Set Values( {"P", "P", "F", "F", "F"} )
	)
);

bad_rows = [];
for(i=1, i<=ncols(dt), i++, 
	col = Column(dt, i);
	
	if(col << Get Data Type == "Character", //this is an assumption that your P/F cols are the only char
		if(nrows(bad_rows), //check if there's anything to clear
			col[bad_rows] = ""; // clear character
		);
		bad_rows |/= loc(col <<Get Values, "F"); // add the rows that you want to clear
	, 
		if(nrows(bad_rows), //check if there's anything to clear
			col[bad_rows] = .; // clear numeric
		);
	)
);