42287_en-US

liliconcom

Get Rows Where command only works on a specific column

Hello,

 

I'm using the "get rows where" command to replace numerical values with a "P" or "N" depending on whether they hit a threshold value. 

 

Here's the code:

 

dt = Open( "C:\test_data.jmp" );
thresh = 12; //User sets this threshold value

 

Try( dt:HOOK[dt << get rows where( :Hook >= thresh )] = "P" );
Try( dt:HOOK[dt << get rows where( :Hook == "" )] = "N" );

 

So this works well and populates the column with N's and P's. If I copy and paste those two try lines and change the column designation it won't work on the other columns. If I change the order of the commands or change the order of the columns, it still only does the Hook column. 

 

What am I missing? 

 

Thanks! 

 

K

Re: Get Rows Where command only works on a specific column

Since the columns are numeric, the comparison 

Try( dt:HOOK[dt << get rows where( :Hook == "" )] = "N" );

will not work.  You should use is missing instead:

Try( dt:HOOK[dt << get rows where( is missing(:Hook))] = "N" );

But as others have said the assignment to "N" will not work because the column is numeric.

Re: Get Rows Where command only works on a specific column

Yes.....it will do that, however, it isn't a good software practice to do this.  I would suggest that rather than setting values to "P" or "N", that you change the values to other numeric values and that you use the column property, Value Labels to change the numeric values you specified, to be displayed as a "P" or "N".  That way you can continue to properly compare to numeric values like you do with the "Thresh" value, but the display gives you what you want.

Re: Get Rows Where command only works on a specific column

I think that @txnelson is right.

 

Remove the Try() from your attempts so you can see the errors in the log.

 

Here's an example of the error you will receive when trying to assign a character value to a numeric column.

 

//:*/
dt=open("$SAMPLE_DATA\Big Class.jmp");
thresh=65;

dt:height[dt << get rows where( :height >= thresh )] = "P";
/*:

Column "height" requires numeric values in access or evaluation of 'Bad Argument' 

 

Re: Get Rows Where command only works on a specific column

Not trying to be contrary, but when it works for that one cloumn, it converts the column automatically to character upon insertion of the P's and N's. 

Re: Get Rows Where command only works on a specific column

If your columns are Numeric and Continuous, then setting the values to "P" or "N" will not work.   You can set the cells equal to a numeric value, but not to a character value.

Re: Get Rows Where command only works on a specific column

I actually considered that as well (my trouble-shooting is not too shabby for a newbie), but when I looked at column info, all three columns are set as "Numeric" & "Continuous."

Re: Get Rows Where command only works on a specific column

There is an issue with your columns being either numeric or character.  All cells in a column must be either character or numeric.  In your code, you are asking column Hook to be compared with a numeric value of "Thresh".  "Thresh" has a numeric value of 12.  You then want to set the value of Hook to either a character "P" or "N"..

Are the columns Hook, Rounds and Whip numeric or character?

Re: Get Rows Where command only works on a specific column

I wish it were *that* simple. The additional code is below. Is there some sort of file operator I'm missing-- like you need to close or save after each column substitution?

 

 

 

Names Default To Here( 1 );

　

dt = Open( "C:\test_data.jmp" );

thresh = 12; //User sets this threshold value (defines rows in output)

　

Try( dt:HOOK[dt << get rows where( :Hook >= thresh )] = "P" );

Try( dt:HOOK[dt << get rows where( :Hook == "" )] = "N" );

　

Try( dt:ROUNDS[dt << get rows where( :Rounds >= thresh )] = "P");

Try( dt:ROUNDS[dt << get rows where( :Rounds == "")] = "N");

　

Try( dt:WHIP[dt << get rows where( :Whip >= thresh )] = "P");

Try( dt:WHIP[dt << get rows where( :Whip == "")] = "N");

Re: Get Rows Where command only works on a specific column

Can you show us the lines after you cut and paste and change the Hook value?

 

My guess is that you are not changing one of the 2 places in both columns where Hook appears.