turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How can I use the select matching rows command in a column formula?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 10, 2015 11:00 AM
(2153 views)

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?

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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" ) ),

.

)

)

);

JMP Systems Engineer, Pharm and BioPharm Sciences

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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**)**

**)**