224980_en-US

liliconcom

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: 

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!

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!!

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=""></GET>