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

- JMP User Community
- :
- Discussions
- :
- Re: Finding the maximum of a column by matching another column condition

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

Highlighted

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

Sep 10, 2020 10:09 AM
(280 views)

Hi every one,

I have to find the maximum value in a column (:PMA Counter) after checking the condition in another column (: Condition). the condition column has "After" and "Before" characters for the different rows.

I want to pick those which are "After" and fin the maximum of (:PMA Counter) between them. I already use the below script but it is not working and only give me the Max without considering the Condition column.

dt=Current table();

dt<<Select Where(Contains (:Condition ,"After" ));

dt<<New Column ("Current PMA Counter", Character,Nominal, Formula(Col Max(:PMA Counter)));

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

The row Select is not honored by the formula. Here is a way to handle what you need

```
dt << New Column( "Current PMA Counter",
Character,
Nominal,
Formula( Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) ) )
);
```

Jim

Highlighted

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

I mistakenly created the Current PMA Counter column as a character string, and it needed to be numeric.....the JSL below seems to work

```
names default to here(1);
dt=current data table();
dt << New Column( "Current PMA Counter",
Formula(
If( Row() == 1,
x = Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) )
);
If( Contains( :Condition, "After" ),
y = x,
y = .
);
y;
)
);
```

Jim

11 REPLIES 11

Highlighted

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

The row Select is not honored by the formula. Here is a way to handle what you need

```
dt << New Column( "Current PMA Counter",
Character,
Nominal,
Formula( Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) ) )
);
```

Jim

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

Thank you very much Jim

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

Can I have the Maximum only in the rows related to "After" rows?

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

I do not understand what you are asking for.

Jim

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

I meant only those rows that had "After" in the Condition column get the Max value in the Current PMA Counter column. Other rows remain blank.

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

Here are a couple of versions of the formula(They are not tested). They differ in what the Col Max is based on

```
// formula where col max is based upon only rows with "After" in PMA Counter
// and the Col Max only appears in rows where PMA Counter has "After" in it
dt << New Column( "Current PMA Counter",
Character,
Nominal,
Formula(
If( Row() == 1,
x = Col Max( If( Contains( :Condition, "After" ), :PMA Counter, . ) )
);
If( Contains( :Condition, "After" ),
y = x,
y = .
);
y;
)
);
// formula where col max is based upon all rows
// and the Col Max only appears in rows where PMA Counter has "After" in it
dt << New Column( "Current PMA Counter",
Character,
Nominal,
Formula(
If( Row() == 1,
x = Col Max( :PMA Counter )
);
If( Contains( :Condition, "After" ),
y = x,
y = .
);
y;
)
);
```

Jim

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

Thanks, Jim

it is finding the value but for some reason does not show up in the column. THE current PMA Counter is all blank

it is finding the value but for some reason does not show up in the column. THE current PMA Counter is all blank

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

can you attach a sample data table that you are applying this to?

Jim

Highlighted
##

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

Re: Finding the maximum of a column by matching another column condition

You can see the data attached.