topic Re: How to select rows with multiple criteria combined in Discussions

How to select rows with multiple criteria combined

ccharlie — Fri, 13 Jan 2017 19:45:21 GMT

Hi,

I'm trying to isolate a specific group within a large data set using the following criteria: all subjects who answered yes to 2 particular questions AND all subjects who answered yes to any 3 other questions combined (total number of questions = 9). I was able to isolate subjects meeting the first criteria rather easily but don't know how to select subjects who meet the second criteria (assuming all subjects who met the 1st one were removed from the sample of interest to avoid counting them twice). Any help would be greatly appreciated!

Re: How to select rows with multiple criteria combined

txnelson — Fri, 13 Jan 2017 21:00:25 GMT

Below is a "Select Rows Where" script that shows the logic of what you need to do. If you want to do this interactively, just take the logic component of the statement and build it in:

Rows==>Row Selection==>Select Where

dt = Current Data Table();
dt << select rows where(
	q1 == "Yes" & q2 == "Yes" & Sum(
		q3 == "Yes",
		q4 == "Yes",
		q5 == "Yes",
		q6 == "Yes",
		q6 == "Yes",
		q7 == "yes",
		q8 == "Yes",
		q9 == "Yes"
	) == 3 /* or >= 3 if that is what you want */
);

Re: How to select rows with multiple criteria combined

ccharlie — Sat, 14 Jan 2017 20:48:24 GMT

Thank you so much for the quick reply Jim. Using my variables and 1 as the yes code, I tried what you gave me:

dt = Current Data Table();

dt << select rows where(

testendurance_T1 == "1" & teststrong_T1 == "1" & Sum(

testenjoy_T1 == "1",

testsad_T1 == "1",

testsports_T1 == "1",

testsleep_T1 == "1",

testperform_T1 == "1",

testlibido_T1 == "1",

maleenergy_T1 == "1"

) >= 3 /*

);

and got the following error:

Unexpected end of input. Perhaps there is a missing "," or ")". Trying to parse arguments of function "select rows where". Line 12 Column 4: );►

I checked row 12 and it does not meet any of the inclusion criteria. I'm clearly missing something but can't figure out what.

Thanks again!

Re: How to select rows with multiple criteria combined

Mark_Bailey — Sat, 14 Jan 2017 21:19:50 GMT

You started a comment on the next to last line, so the closing parenthesis is not seen.

Re: How to select rows with multiple criteria combined

txnelson — Sat, 14 Jan 2017 21:19:00 GMT

Mark's comment below is correct. In the example that I gave you, it had a comment

/* or >= 3 if that is what you want */

embedded in a line of code. This type of comment starts with "/*" and looks for "*/" to end the comment. Since your code just has the /*, the ");" on the next line is considered part of the on going comment

Re: How to select rows with multiple criteria combined

ccharlie — Sun, 15 Jan 2017 20:28:26 GMT

Thank you all! I tried modifying the code but it still didn't work so I went into the JMP Scripting Guide to try to figure things out. I was able to select all rows using this code:

dt = Open( "myfile.jmp" );

dt<<Select All Rows;

Since this worked I tried to go for a simpler code than the original one I had to see if I could get things on track using the JMP instructions. I tried to following code unsuccessfully:

dt = Open( "myfile.jmp" );

dt<<Select Where( :ID==10322);

I ran it with no error message coming up but nothing happened. I checked the specific row which should have been selected but it was still unselected. I tried again but still nothing. Going in the script log, I found the following:

//:*/

dt = Open( "myfile.jmp" );

dt << Select Where( :ID==10322 );

/*:

Scriptable[]

//:*/

dt = Open( "Pre_Post_Freshman_Screener_.jmp" );

dt << Select Where( :ID==10322 );

I tried switching the code to ID=10322 to see if it would make a difference and got the following:

/*:

//:*/

dt = Open( "Pre_Post_Freshman_Screener_.jmp" );

dt << Select Where( :ID=10322 );

/*:

Use == for comparison, = is assignment

Use == for comparison, = is assignment{1}

//:*/

dt = Open( "Pre_Post_Freshman_Screener_.jmp" );

dt << Select Where( :ID=10322 );

/*:

Use == for comparison, = is assignment

Use == for comparison, = is assignment{1}

//:*/

dt = Open( "Pre_Post_Freshman_Screener_.jmp" );

dt << Select Where( :ID=10322 );

/*:

I can't help but think that this must be a simple error but I'm still stumped. Any thoughts?

Thanks again for your help!

Re: How to select rows with multiple criteria combined

Jeff_Perkinson — Sun, 15 Jan 2017 20:56:08 GMT

Is your ID column character or numeric?

Remember that character columns are displayed right justified and numeric columns are left.

If your column is character you'll need to put the ID value in double quotes for the comparison.

For example:

:ID=="1234"

Re: How to select rows with multiple criteria combined

Jeff_Perkinson — Sun, 15 Jan 2017 21:05:14 GMT

Is your ID column character or numeric?

Remember that character columns are displayed ~~right~~ left justified and numeric columns are ~~left~~ right.

If your column is character you'll need to put the ID value in double quotes for the comparison.

For example:

:ID=="1234"

Re: How to select rows with multiple criteria combined

Jeff_Perkinson — Sun, 15 Jan 2017 21:04:37 GMT

@Jeff_Perkinson wrote:

Remember that character columns are displayed ~~right~~ left justified and numeric columns are ~~left~~ right.

I got my left and my right messed up above. Sorry!

-Jeff

Re: How to select rows with multiple criteria combined

ccharlie — Sun, 15 Jan 2017 22:51:29 GMT

Thank you for the quick response Jeff! Adding the " " did the trick as far isolating the row matching that particular ID number. Since I don't actually know if any row matches the criteria of a "yes" (coded as 1) answer to at least 3 questions from the set of 7 questions mentioned before, I tried to do enter a sum command for a couple variables which I knew should return something. Here is what I tried:

dt = Open( "myfile.jmp" );

dt << Select Where( Sum( :Fruit_Screener == "2" & :Vegetables_Screener == "2" ) == 4 );

Unfortunatelt nothing happened. I do know that at least one row meets the criteria of "no" (coded as 2) for these two variables but no selection occurred. Could the _ in the variable names be an issue or is it an error with the sum formula?

Thanks again!

Re: How to select rows with multiple criteria combined

txnelson — Sun, 15 Jan 2017 22:58:43 GMT

You seem to be missing how the logic is working. What you have below can never have a sum greater than one.

a Boolean comparison always results in a 0 or a 1.

So if you were checking to see if a variable called X has a value of 22, the comparitor or

X==22 will return a 1 when it is true, and a 0 when it is false.

in your example below, the comparitor of

Fruit+Screener == "2" & Vegitables_Screener == "2" can only be true(1) or False(0).

The sum of this can only be a 0 or a 1, never a 4

Re: How to select rows with multiple criteria combined

ccharlie — Sun, 15 Jan 2017 23:27:33 GMT

Thanks Jim! I tried replacing that 4 with a 1 since I know this combination to be true of at least one row but still nothing was selected. Overall, I am looking to select rows which have "yes" (coded as 1) as answers for at least 3 questions out of a group of 7 total questions (so >=3 total). Any tips would be greatly appreciated.

Thanks again!

Re: How to select rows with multiple criteria combined

txnelson — Mon, 16 Jan 2017 01:22:27 GMT

I suspect that Fruit_scanner and Vegitable_Scanner columns are numeric, not character. In your "Nonworking" example, you specified the comparison as:

Fruit_Scanner=="2".

If Fruit_Scanner is a numerical column the comparison would be:

Fruit_Scanner==2

Also look at the actual values of Fruit_scanner. I thought you indicated that the values were 0 or 1. If that is true then you need to be looking for:

Fruit_Scanner==1

The original reply that I published is the format that needs to be used to get what you want. What is needed are the actual names of the columns and the actual values in those columns. If you can provide that, I think I could give you the JSL

Re: How to select rows with multiple criteria combined

ccharlie — Mon, 16 Jan 2017 16:51:52 GMT

Thanks for the information! Modifying the Fruit_Scanner=="2" to Fruit_Scanner==2 worked like a charm. Using that I was finally able to get the rows I was looking for using the following:

dt = Open( "myfile.jmp" );

dt << Select Where( :testendurance_T1 == 1 | :teststrong_T1 == 1 | Sum( :testenjoy_T1 == 1, :testsad_T1 == 1, :testsports_T1 == 1, :testsleep_T1 == 1,

:testperform_T1 == 1, :testlibido_T1 == 1, :maleenergy_T1 == 1 ) >= 3 );

Thanks again all for everything!