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

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

BSwid — Thu, 11 Apr 2019 14:31:41 GMT

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:

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

Mark_Bailey — Thu, 11 Apr 2019 16:19:45 GMT

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 (=!)

Mark_Bailey — Thu, 11 Apr 2019 16:21:16 GMT

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

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

BSwid — Thu, 11 Apr 2019 17:56:27 GMT

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 (=!)

Mark_Bailey — Thu, 11 Apr 2019 18:04:12 GMT

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.

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

BSwid — Wed, 17 Apr 2019 16:59:05 GMT

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

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

BSwid — Wed, 17 Apr 2019 17:00:26 GMT

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.