select where statement for column value comparision

[#1]

Got back to JSL after a long break and feeling rusty.

Can't get a select where statement work.I'm sure it's pretty simple.

I'm trying to select rows by comparing a value with values in two other columns.

dt << select where( :Col1 > :Col2 & :Col1< :Col3);

Col1 is character(on purpose) because it needs to be used further and Col2,Col3 are numeric cols.

What is wrong?

I even tried doing this from GUI but does'nt seem to work.

Thanks

[#2]

For </> comparisons, all the columns need to be numeric.

[#3 · Accepted solution]

Hi @ENTHU,

I can see a few things you'll need to address. I take it you're trying to reference these columns by their occurrence in the data table? If so, you'll need to adjust the syntax to make the reference in that manner (i.e. :Column(#)[] ). Also, if your first column is a character data type, you'll need to wrap the reference to that column in the num() function to convert the strings to a numeric value in order to do the comparison. Here's some code to get you started:

dt = current data table();
dt << select where( num(:Column(1)[]) > :Column(2)[] & num(:Column(1)[]) < :Column(3)[]);

I hope this helps!

@julian 

[#4]

Hi @ENTHU,

  As mentioned by @mzwald, the comparisons >, <, etc. require numerical values to work with.

  One idea to get around this for your :col1 (which is nominal), is to use numerical coding, like 0, 1, 2, 3, 4 for the different character classes (if you can), and then use the column property "value labels" to put the characters in the label role. That way, you can perform the comparisons, but at the same time when you graph or display the column contents, it will show the value labels instead.

  I tested it out with a simple 3-column table and same comparison code that you wrote. Colmn 1 has values 0-4, Column 2 is Random Normal(0), and Column 3 is Random Uniform(-1,1). In this case, it selected row 6.

Hope this helps!,

DS