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.
Choose Language Hide Translation Bar
Newbie2Jumpie
Level IV

How to filter / cleanse data JMP using a list [tipp, JSL, scripting]

After receiving so much great support, I thought it was about time I returned this favour. Below you'll find a little program that might make it easier to filter data tables. Hard encoding by "entry" is one way to filter entries; this is tedious and prone to typos. An alternative is to filter entries by their "position" in a filter list. This approach just assumes that the sequence of fields in the filter list does not change. To make sure, just check the JMP log. As a quality check, it will tell you which entries were excluded .

Good luck

Newbie2Jumpie

			names default to here( 1 );
			// Open a sample data table
			dt = Open( "$SAMPLE_DATA/big class.jmp" );

			/* Get entries/levels from Column NAME and store them in NAME_LIST */
                    NAME_LIST = dt:NAME << get values ;
					NAME_LIST  ;					
			/* Remove selected entries ("levels") on given position in list = JMP table */
	                remove from(NAME_LIST, {1,3,5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
			/* Cleans data by removing rows using level list  */
	            dt << Select where(contains(NAME_LIST, :NAME)) ;
	      	    dt_2 = dt   << subset(selected rows(1),  all columns);
	      	    
  	    	/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
				close (dt, nosave) ;	

 

2 REPLIES 2
Newbie2Jumpie
Level IV

Re: How to filter / cleanse data JMP using a list [tipp, JSL, scripting] - How to make it work for duplicates?


Hi all,
Last Friday I provided a small JMP that helps to filter data by their "position" in a filter list.
In a PM I received a message that this works only as long the entries scanned for the filter list are unique. For example, if alle entries filtered from NAME are unique, e.g. if you want to filter out "Katie"; "Jane" or "Lillie". Now, "Robert" appears two times in "BIG CLASS". As soon as you want to exclude "Robert" too, then this approach gets stuck. Any idea how to solve that?
Newbie2Jumpie
			names default to here( 1 );
			// Open a sample data table
			dt = Open( "$SAMPLE_DATA/big class.jmp" );

			/* Get entries/levels from Column NAME and store them in NAME_LIST */
                    NAME_LIST = dt:NAME << get values ;
					NAME_LIST  ;					
			/* Remove selected entries ("levels") on given position in list = JMP table */
	                remove from(NAME_LIST, {1,3,5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
			/* Cleans data by removing rows using level list  */
	            dt << Select where(contains(NAME_LIST, :NAME)) ;
	      	    dt_2 = dt   << subset(selected rows(1),  all columns);
	      	    
  	    	/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
				close (dt, nosave) ;	

 


 

txnelson
Super User

Re: How to filter / cleanse data JMP using a list [tipp, JSL, scripting] - How to make it work for duplicates?

Here is one way to do it.  The only complexity is to convert the matrix of row numbers into a list

Names Default To Here( 1 );
// Open a sample data table
dt = Open( "$SAMPLE_DATA/big class.jmp" );

/* Get entries/levels from Column NAME and store them in NAME_LIST */

eval(parse("NAME_LIST =as list (" || char(index(1,n rows(dt))) || ")[1];"));

NAME_LIST ;					
/* Remove selected entries ("levels") on given position in list = JMP table */
Remove From( NAME_LIST, {1, 3, 5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
/* Cleans data by removing rows using level list  */
dt << Select where( Contains( NAME_LIST, Row() ) );
dt_2 = dt << subset( selected rows( 1 ), all columns );
	      	    
/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
Close( dt, nosave );
Jim