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
BSwid
Level IV

Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

Looking for some help to understand the behavior or a better way to script this.

Writing a script to catch exceptions in the data. When version is GP but active is not X.

Names Default To Here( 1 );
dt = Current Data Table();
df = dt << Data Filter(
	Location( {138, 181} ),
	Add Filter(
		columns( :Active, :Version ),
		Where( :Active != "X" ),
		Where( :Version == "GP" )
	)
);

Then the rest of the script subsets that data and creates a tabulate from that subset.

 

The problem I am encountering is when all rows have Active == X.  The filter is selecting all rows.  I suppose that's because it's not finding an alternative to X which is blank (not missing).  What's the best way to handle this?

 

Result of the above code:
JMP Community Data Filter Behavior.png


 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

OK, it actually makes your scripting easier. No pesky human interaction involved!

 

The data table message << Select Where( Boolean expression ) will do it all for you. The Boolean expression can be simple (e.g. :Age == 12) or complex (e.g., :Age == 12 & :Sex == "F"). Any function that returns a result that can be interpreted as a Boolean (i.e., 0 is false, not 0 is true) will work. This requirement means that you can use query functions such as Contains( source column, "target" ) for literal targets or Regex( source column, regex ) for patterns.

 

After you select the rows you want, then send the << Subset() message to extract the data.

View solution in original post

6 REPLIES 6

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

You could first check to see if the condition can be met:

 

target rows = Current Data Table() << Get Rows Where( :Active != "X" );

If( N Row( target Rows ),
   ...
);

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

Just checking: why are you using a data filter object? Does the user have to interact for the sake of the script?

BSwid
Level IV

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

No need for the user to interface.  Just a novice stealing code after each step done manually and then constructing the code from those steps.  I assume there's a better way to do select the subset and create the subset table?

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

OK, it actually makes your scripting easier. No pesky human interaction involved!

 

The data table message << Select Where( Boolean expression ) will do it all for you. The Boolean expression can be simple (e.g. :Age == 12) or complex (e.g., :Age == 12 & :Sex == "F"). Any function that returns a result that can be interpreted as a Boolean (i.e., 0 is false, not 0 is true) will work. This requirement means that you can use query functions such as Contains( source column, "target" ) for literal targets or Regex( source column, regex ) for patterns.

 

After you select the rows you want, then send the << Subset() message to extract the data.

BSwid
Level IV

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

This is very helpful. Thank you.

 

I ended up doing something like the following. 

Scripting question, Is there a better way do to it?  As in did I need the CLE_TL intermediate?  Or can it just be dt << select where << subset  on one line?  Is there a benefit to creating that CLE_TL object?

names default to here(1);
dt = current data table();

shipPlant = "CLE";
shipMode = "truckloads";
subsetTable = "CLE_truckloads";

//filter
CLE_TL = dt << select where(:TransportPlanningPt == shipPlant & :MODE == shipMode);

//subset
CLE_TL  << Subset(
	Output Table( subsetTable ),
	Selected Rows( 1 ),
	Selected columns only( 0 )
);

 

 

BSwid
Level IV

Re: Data Filter - Selecting all when there is no alternative value to where does not equal statement (=!)

Quick note.  I used your suggestion to help with one analysis. On to the next.  That's why the coding doesn't match the screen shot.