JSL: DT<

[#1]

Normal "SelectWhere" usage appears to be as follows:

dt<<SelectWhere(:ExampleColumnName > 5);

And this works fine. What I have difficulty with is when I need more flexiblity in what the ":ExampleColumnName" is going to be.

For example, if I save a new column of Studentized Residuals in a Fit Model, it will create a column called something like "Studentized Resid Log(CFU/mL) By Condition". Let's say I want to select anything above 3 or below -3. Due to the column's name, I would need to use:

dt<<SelectWhere(:Name("Studentized Resid Log(CFU/mL) By Condition")<-3 | :Name("Studentized Resid Log(CFU/mL) By Condition")>3);

This is fine most of the time, but not always. Having to have the fully written out string within :Name() isn't really ideal -- for example, if the plan is to make a function usable for a variety of Fit Models that will each produce different Studentized Residual column names, it isn't efficient to write a unique "SelectWhere" for every possible model (especially if you can't always anticipate what model the user will choose).

What I would like to work is one/both of the following:

//Option 1
colName = ""Studentized Resid Log(CFU/mL) By Condition";
dt<<SelectWhere(:Name(colName)<-3 | ::Name(colName)>3);

//Option 2
colRef = Column(dt, colName);
dt<<SelectWhere( colRef <-3 | colRef >3 );

Option 1 returns a "Name argument must be quoted string" error when the SelectWhere function is run. Seems like :Name() requires a fully written out string, and a reference to one is unacceptable.

Option 2 returns no errors, but nothing will be selected. SelectWhere doesn't seem to treat column reference variables like explicitly typed out ":XXXX" references.

My current workaround is not great: after Fit Model creates new column, rename the very last column on the table to something simple and unique (like "StdResid") that can be directly used in the "normal usage" of SelectWhere (e.g., ":StdResid").

Am I missing something obvious here that would make my life easier?

Also, is there any way to get Option 1 and/or Option 2 to work?

[#2]

Here is an approach that I use, when nothing else seems to be getting me the results I need:

Eval(parse("dt << select where(" || ":Name(\!"" || Char( Column( dt, 1 ) << get name ) || 
"\!")< -3 | :Name(\!"" || Char( Column( dt, 1 ) << get name ) || "\!")  > 3);"));

[#3]

JSL and column names are confusing, especially when you're trying to use GET ROWS WHERE or SELECT WHERE.  I tend to use the construct as column(dt, "column-name").

This will work:

dt = New Table( "Test Table", Add Rows( 9 ),
	New Column( "Studentized Resid Log(CFU/mL) By Condition",
		Numeric, Continuous, Format( "Best", 12 ),
		Set Values( [-4, -3, -2, -1, 0, 1, 2, 3, 4] ) )
);

colName = "Studentized Resid Log(CFU/mL) By Condition";

dt << Select Where(as column(dt, colName) < -3 | as column(dt, colName) > 3);

[#4]

Both great ideas, thanks. I had been trying to use Column(dt, colName) before and didn't know that AsColumn() existed.

I would still love to see the functionality from Option 1 and Option 2 implemented some day. What's the best way to ask for this?

[#5 · Accepted solution]

Hello, 

I think the important piece to understand is what each returns:

• The :Name() syntax is a special parser that only accepts a string argument, as you saw.  This makes it quite inflexible for scripting.
• The Column() function is returning a reference to the column as a whole.  Select Where() performs its comparisons on each row.  Adding a Row() subscript  (or just []) to the Column() function will instruct JMP to look at the column on the current row, whatever that is while the Select Where() is performing its comparison. 

colName = "Studentized Resid Log(CFU/mL) By Condition";

dt << Select Where( Column( colName )[] < -3 | Column( colName )[] > 3 );

• The As Column() function returns a reference to the value in the specified column on the current row.  In the context of a Select Where(), the current row is being controlled by the Select Where().  That is why the Row() subscript is not necessary for @pmroz's example.  

Hope that helps!

[#6]

I use the As Column most of the time.  It works with either a string name or column reference:

dt = Open("$SAMPLE_DATA/Big Class.jmp");

// column name
col = "height"; 
dt << Select Where( AsColumn(col) <60 );

Wait(2);
dt  << Clear Row States;

// column reference
col = Column(dt,"height");
dt << Select Where( AsColumn(col) > 65 );

If I feel the need to embed an actual text string into :Name on the fly then I use the other method shown, although I use other syntax that I find easier to deal with:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
colName = "height";
Eval(Parse(EvalInsert("\[ 
	dt << Select Where( :Name("^colName^") > 65 );
]\")));