topic Re: data filter favorites --can I use "not equal" in where clause? in Discussions

Can I use the "not equal to" operator in Data Filter favorite "where" clause?

jmpbeginner — Thu, 16 Aug 2018 19:38:42 GMT

I am trying to define a filter favorite that will scale as new data is added to my table:

Favorites(
  "Test_Favorite1"(Match( columns( :type, :name, :batchID ),
  Where( :type == "PM" ),
  Where( :name == "Weekly" ),
  Where( :batchID == {266, 268, 290} )
  ))
)

I would like to be able to specify what batchIDs to NOT include in the filter, that way as new batchIDs show up my filter will include them (but exclude the problem batchIDs that I have previously identified to exclude). When I try to use the != operand, I get errors.. I also tried !== as well with no luck.

//example (include ALL batchIDs except for 268):
<!--
 
Favorites(
  "Test_Favorite1"(Match( columns( :type, :name, :batchID ),
  Where( :type == "PM" ),
  Where( :name == "Weekly" ),
  Where( :batchID != {268} )
  ))
)

I don't see anything in the manuals/scripting guide.. is this possible?

Re: data filter favorites --can I use "not equal" in where clause?

mwechtal — Thu, 28 Jan 2016 15:40:34 GMT

I would try:

Where( not(:batchID == {268}) )

I've had other situations where the != operator didn't work, and this seems to get around that.

Re: data filter favorites --can I use "not equal" in where clause?

jmpbeginner — Thu, 16 Aug 2018 19:41:57 GMT

dang, no luck:
"The WHERE clause has not been correctly specified. in access or evaluation of 'Not' , !/*###*/(:experiment_id == {303, 322})"

i tried this:

Favorites(
"Test_Favorite1"(Match( columns( :type, :name, :batchID),
Where( :type == "PM" ),
Where( :name == "Weekly" ),
Where( Not( :batchID == {303, 322} ) )
))
)

maybe I need to select all batchID's first, then the Where(NOT()) will work?? Is there a way to select all batchID's on launch without calling out a static list of batchID numbers?

thanks

Re: data filter favorites --can I use "not equal" in where clause?

mwechtal — Fri, 29 Jan 2016 21:57:49 GMT

Hmm, I'm stumped. You might have to go to a Script to do that.

Re: data filter favorites --can I use "not equal" in where clause?

MikeD_Anderson — Thu, 16 Aug 2018 19:41:17 GMT

Try this:

Favorites(
"Test_Favorite1"(Match( columns( :type, :name, :batchID ),
Where( :type == {"<Not PM Entry 1>","<Not PM Entry 2>","<Not PM Entry 3...>"} ),
Where( :name == {"<Not Name Entry 1>","<Not Name Entry 2>","<Not Name Entry 3...>"} ),
Where( :batchID == {303, 322} )
)),
Inverse( 1 )

);

You'll have to fill in the missing type and name entries, but this should do the exclusion of the specific batchID's. If that doesn't do what you're after, I'd recommend that you send a note to tech support (support@jmp.com). They have some really good scripters on the team that have helped me out in the past. Also if its something that JMP can't do, they can route the question to the developers to see if it can be fixed in a future issue of JMP.

Best,

M