How can I use the select matching rows command in a column formula?

Question

I want to make a permanent column formula that will automatically select rows where two columns have the identical data.  Then use this selection to find a minimum value in one column of the rows selected.  I know I can do it with a for loop and script, is there anyway to just embed the "select matching rows" command into a column formula?

ms · Answer

Not sure I understand, but it seems that you look for the minimum of a variable (e.g. height) by two grouping columns (e.g. age and age2).

Then you could use this formula:

Col Minimum(:height, :age, :age2)

Your condition "...if there are no matches then do not list any minimum..." is not clear to me and the formula above would always return a minimum as there will be at least one "match", i.e the row itself. The formula below should return a missing value instead of the "minimum" if there is only one occurrence of a combination of age and age2.

If(Col Number(:height, :age, :age2) > 1,

Col Minimum(:height, :age, :age2)

)

rleeper · Answer

Byron,

Not quite.  I need to know when two sets match:  Example:

In Row 1 - Age = 12, Age2 =13, show me any other rows where Age =12 & Age2 =13 in the same row, and this might occur 20 times in the table, so out of those 20 times, pick the column minimum from just those 20 rows.

Now go to row 2 and do the same comparison for its values, if there are no matches then do not list any minimum.

Byron_JMP · Answer

Are you trying to do something like this?

In this case when :age=:age2, then I get an indicator variable. Using col minimum() I can get the minimum height, by age, by indicator (but only when the indicator isn't missing)

(run this script and take a look at the table's formulas)

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

dt << New Column( "age 2",

Numeric,

"Continuous",

Format( "Best", 12 ),

Set Values(

[14, 12, 13, 13, 13, 14, 12, 15, 13, 12, 14, 14, 13, 15, 14, 12, 14, 15, 13, 13, 14, 15,

15, 15, 14, 15, 12, 15, 15, 15, 14, 13, 15, 15, 14, 15, 12, 14, 13, 13]

)

);

dt << New Column( "age == age 2",

Numeric,

"Nominal",

Format( "Best", 12 ),

Formula( If( :age == :age 2, 1, . ) ),

Set Selected

);

dt << New Column( "minimum height where age=age2",

Numeric,

"Nominal",

Format( "Best", 12 ),

Formula(

If( Is Missing( :Name( "age == age 2" ) ) == 0,

Col Minimum( :height, :age, :Name( "age == age 2" ) ),

.

)

);