34840_en-US

liliconcom

How do I use the Col Maximum Formula with a "where" condition

Hi, I have a problem where I want to formulate a new column as a benchmark to all other countries. If there are more rows for the country, I want to take the max value of it as the benchmark. See the example below for clarification.

 

Data kind of looks like this:
Country       Value         period       

A                  10              201301         

A                  30              201301          

B                  20              201301          

C                  50              201301

D                  40              201301

 

What I want to achieve (with cell formula): Col Maximum (:value, :period, :country) where :country == "A"

Output:

Country       Value         period        BenchMark

A                  10              201301          30

A                  30              201301          30

B                  20              201301          30

C                  50              201301          30

D                  40              201301          30

 

As you can see, I want to benchmark the max value of only country A.

I tried Col Maximum (:value, :period, :country == "A") and it will return "50"

Col Maximum (:value, :period, :country) and Col Maximum (:value, :period, :country == "A") both returns "50"

 

I am pretty sure there's a simple solution for this!

 

Hope you guys can help!

 

Thanks thanks!

Re: How do I use the Col Maximum Formula with a "where" condition

Interesting question - sill waiting for an answer?

 

There is a set of posts with similar questions how to restrict the Col ... aggregation functions to a specific subset.
(mainly: restrict them to the subset of non-excluded rows)

https://community.jmp.com/t5/Discussions/Compute-Column-Mean-while-restricting-observations-used/m-p/10377#M10055

https://community.jmp.com/t5/Discussions/JMP10-Col-Max-and-Col-Min-functions-returned-the-data-from/td-p/10555

https://community.jmp.com/t5/Discussions/Col-Functions-and-Row-States/td-p/351705

 

The idea: 

If one is interested in the mean height, one can aggregate over all rows - or one can aggreate male and female entries separately.

For the second one the solution is almost as easy as for the first one: just use an additional GroupBy variable to split between wanted and unwanted data.

 

But, what a user often needs in addition: the values should  show up in all respective rows (i.e. also for female).
[The example with the benchmark column in the original post illustrates why such a behavior could make sense.]

With the GroupBy, it's easy to get the correct values - but how to distribute them over ALL rows.

 

If there is something like a lookup table behinde a Col ... aggregation function, the function uses the same identifier
1) to split rows to different groups for aggregation ("M" / "F"):
    Judy is female, the weight in Judy's row should be included in female mean weight
2) as index when reading an entry from the lookup table ("M" / "F"):
    Judy is female, the new column should contain female mean weight in Judy's row

 

So, either male and female get the same values, because the identifier is the same (mean1) - or they get different values because the identifier is different (mean2).

ergo: 
A Jmp user can use the GroupBy feature to remove values from the calculation via:

- calculate different values for different subgroups
- dump the unwanted results
but then he gets
- get different values for different subgroups
for free - even if the he did not order this option (OK, the code tells another story - but let's  think about what the user wanted) 

 

Is there a one-line command to remove female from the mean calculation, but to write the values into the respective rows with "female"?

 

 

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << New Column("mean1", Formula(Col Mean(:weight, :age)));
dt << New Column("mean2", Formula(Col Mean(:weight, :age, :sex)));
dt << New Column("mean2", Formula(If(:sex=="M", Col Mean(:weight, :age, :sex))));

dtM = dt << subset( Rows( dt << get rows where( :sex== "M" ) ), selected columns( 0 ));
dtM << New Column("mean_M", Formula(Col Mean(:weight, :age)));
dt << Update(
 With( dtM ),
 Match Columns(:age = :age ),
 Add Columns from Update Table( :mean_M ),
 Replace Columns in Main Table( None )
);

 

Re: How do I use the Col Maximum Formula with a "where" conditionthanks.Re: How do I use the Col Maximum Formula with a "where" condition

Col Maximum() is only viable in a formula, where a separage result per row is the output.  To do what you want, use the Summarize() function.

See:

     Help==>Scripting Index==>Summarize

Re: How do I use the Col Maximum Formula with a "where" condition

Hi Mark,
tried to get maximum weight by age or sex, but only produce one value in the following script.
by ages, there were 6 different ages, for each of them, I'd like to compute the maximum weight for each age.

Names Default To Here( 1 );
Clear Log();
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
max_weight_by_age = Col Max( :weight, :age );
max_weight_by_sex = Col Max( :weight, :sex );

Re: How do I use the Col Maximum Formula with a "where" condition

I could add this formula to a new column in the Big Class example data table:

 

Col Max( :weight, :age )

Re: How do I use the Col Maximum Formula with a "where" conditionHi Mark,
Can you give an example on how to use the when using col Max?

thanks.Re: How do I use the Col Maximum Formula with a "where" condition

See Help > Scripting Index > Functions:

Re: How do I use the Col Maximum Formula with a "where" condition

Thanks Jim, I meant summarize only. How I do it is (two jsl commands):

Summarize on the column, create new table with values (min/max/or any other statistic for the column).

Then I update the main table using the summarized table by matching the row entries.

 

I was just curious if there is conditional option in Max col() command which can identify unique column values and eliminate the need for this extra step. (would eliminate an extra table and an 2 extra steps). 

Appreciate your help. 

Re: How do I use the Col Maximum Formula with a "where" condition

I typically use the Summarize() function, rather than the Summary Platform, to do easy determination of what levels within a column are found.  Then, from there, you just have to generate the formula's you need to get the job done.

Re: How do I use the Col Maximum Formula with a "where" condition

Suppose the country column has several names and I do not know all the possible values. How can this query be tweaked to do that. 

I can do that by creating a summary table and then updating the table back, but that seems to be ineffecient method, was curious if it could be done in a single step like this. 

 

Thanks in advance. 

Re: How do I use the Col Maximum Formula with a "where" conditionSorry for the late reply! Thanks for the solution!Re: How do I use the Col Maximum Formula with a "where" condition

Given the way you stated your needs, Assuming you know what the country that has the multiple entries.

dt = Current Data Table();
rowperiod = :Period[Row()];
Max(
 :Value[dt << get rows where(
  :Country == "A" & :period == rowperiod
 )]
);