BookmarkSubscribe
Choose Language Hide Translation Bar
fsanjuan
New Contributor

Deleting cell values based on condition

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: 

Test1P/F 1Test2P/F 2Test3P/F 3Test4P/F 4Test5P/F 5
1P2P4P9F3P
5P8F4P6F2P
7P1P6F5F6F
4P1P9F9F9F
6F410F10F8F

 

Required Output:

Test1P/F 1Test2P/F 2Test3P/F 3Test4P/F 4Test5P/F 5
1P2P4P9F**
5P8F******
7P1P6F****
4P1P9F****
6F********

 

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!

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User

Re: Deleting cell values based on condition

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
		);
	)
);
Vince Faller - Predictum
2 REPLIES 2
vince_faller
Super User

Re: Deleting cell values based on condition

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
		);
	)
);
Vince Faller - Predictum
Highlighted
fsanjuan
New Contributor

Re: Deleting cell values based on condition

This really solved the problem!!! Kudos!

I was also surprised it ran less than a second for thousands of cells.

 

Thanks!!

0 Kudos