Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted

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.

Learn it once, use it forever!

View solution in original post

6 REPLIES 6
Highlighted

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 ),
   ...
);
Learn it once, use it forever!
Highlighted

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?

Learn it once, use it forever!
Highlighted
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?

Highlighted

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.

Learn it once, use it forever!

View solution in original post

Highlighted
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 )
);

 

 

Highlighted
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.

Article Labels

    There are no labels assigned to this post.