BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
BSwid

Contributor

Joined:

Sep 25, 2018

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
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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!
6 REPLIES 6
markbailey

Staff

Joined:

Jun 23, 2011

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

Staff

Joined:

Jun 23, 2011

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

Contributor

Joined:

Sep 25, 2018

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
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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

Contributor

Joined:

Sep 25, 2018

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

Contributor

Joined:

Sep 25, 2018

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.