Subscribe Bookmark RSS Feed

How to pass a vector to a Where Clause in the Data Filer

tdeborde

New Contributor

Joined:

Aug 14, 2017

Hi, 

I'm trying to learn how to pass a vector to the where clause of the data filter command. I'm trying to figure this out because I can't seem to get a less than equal to statement in the where clause to work. However passing Where ( :columnname == {1,2}), does work. It seems like it should be simple but it ends up proving to be buggy and I can't figure out the proper syntax. Example code shown below.

 

GE_Table = Current Data Table();
GE_Table << DataFilter(Add Filter(
columns(:SUSPECT_COUNT,:SYMPTOM_COUNT),
Where( :SYMPTOM_COUNT == 1),
Where( :SUSPECT_COUNT <=2 ), //this doesn't work but Where( :SUSPECT_COUNT == {1,2}) does...?????
show window(0)
)
);

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

The issue is that the column you are filtering on is not a continuous column, but rather an ordinal or nominal column.  Thus, the different methodology for the filter.

Jim
3 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

The issue is that the column you are filtering on is not a continuous column, but rather an ordinal or nominal column.  Thus, the different methodology for the filter.

Jim
Byron_JMP

Staff

Joined:

Apr 26, 2012

I think you're running in to a modeling type issue.

The suspect_count column is nominal, right?  Since its nominal giving it a list of values works. (it a list because its in curly brackets.) If the column was numeric continuous then your script would work.

 

Take a look at this bit of script.

Age is numeric nominal, and in my filter I picked 12 and 14 (curly brackts, column delimited). But then weight is numeric continuous, so the inequality, "<= 100", works.  A quick way to sort out the syntax is to manually build the filter, and then from the red triangle menu in the row filter, save the script to the clipboard or script window. 

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Data Filter(
	Add Filter(
		columns( :weight, :age ), 
		//Where( :weight >= col minimum(:weight) & :weight <= 100 ),
		Where( :weight <= 100 ),
		Where( :age == {12, 14} ),
		Display( :age, Blocks Display )
	)
);

note on the commented line ("//"): This is just to show that the value can be something from the column (or anything eles that evaluates)   

 

So then for the slightly longer question, "How to I pass values into a list and then use the list in the filter?" (please pardon the hevy paraphrasing)

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
Summarize( age_levels = By( :age) );
show(age_levels);
parse(age_levels[2]);//second thing in the list of age_levels, returns a number
age_list={};//empty list
insert into(age_list,parse(age_levels[2]));//second thing in the list of age_levels, "parse" returns a number not a quoted string
insert into(age_list,parse(age_levels[4]));//add 4th thing
show(age_list);//look at the log to see the values

dt << Data Filter(
	Add Filter(
		columns( :weight, :age ),
		Where( :weight <= 100 ),
		Where( :age == age_list ),//note the list gets evaluated here
		Display( :age, Blocks Display )
	)
);

This script might be a bit easier to read in a JMP script window.

 

tdeborde

New Contributor

Joined:

Aug 14, 2017

Thanks for the replies. The column type switch to continuous was the easiest fix for me, but the examples are helpful to see more code examples. Thanks again!