cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
uday_guntupalli
Level VIII

Get Rows that satisfy a condition within a column formula

All, 

      Probably an easy question - but looking for some help over this. Is there a way to get rows that match a condition within a column formula ? 

       In the e.g.  shown below, one should be able to acheive  the grouped sum of a given group. If I wanted to add a condition on top of this , let us say on :height should be > 60 , how can I achieve that. 

 

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

dt << New Column("CondSum",Numeric,Continuous,Formula(Col Sum(:weight,:age)));

       Outside a column formula , I believe that is easy and can achieve it. However, I would like to implement this through a column formula, if possible. 

Best
Uday
1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: Get Rows that satisfy a condition within a column formula

Hi Uday,

You can actually just type the condition as the next argument.

Col Sum( :weight, :age, :height > 60 )

It may not look right at first in this Big Class example because the sum is the same for the 12 year olds below and above 60 inches.  They just happen to be the same sums.

-- Cameron Willden

View solution in original post

5 REPLIES 5
cwillden
Super User (Alumni)

Re: Get Rows that satisfy a condition within a column formula

Hi Uday,

You can actually just type the condition as the next argument.

Col Sum( :weight, :age, :height > 60 )

It may not look right at first in this Big Class example because the sum is the same for the 12 year olds below and above 60 inches.  They just happen to be the same sums.

-- Cameron Willden
mann
Level III

Re: Get Rows that satisfy a condition within a column formula

Hi, putting a comparison in as the byVar argument is a nice idea:

Col Sum( :weight, :age, :height >= 60 )

  I am interested in putting a comparison in the byVar argument that is comparing it to the current row:

myheight = :height;
Col Sum( :weight, :age, :height >= myheight )

This does not work as expected:  the calculation shows a confusing set of numbers,  and then they all change to "1" if I change someone's height.

Is there a way to do this?

 

(Just for a better frame of reference, in my actual table, I am trying to count rows where the date in colA is greater than the datevalue in colA and where the date in colB is less then the datevalue in colB.) 

vince_faller
Super User (Alumni)

Re: Get Rows that satisfy a condition within a column formula

So I'm guessing it has something to do with 

The result is cached internally so that multiple evaluations will be efficient

Can't say for sure though.  If you just use an intermediate column it seems to work fine.  

 

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

dt << New COlumn("Conditional", Formula(
	myheight = :height;
	:height >= myheight; // should always be 1
	//:height >= .5*weight // this might be a better example
));

dt << New Column("CondSum",Numeric,Continuous,Formula(
	Col Sum( :weight, :age, :Conditional );
));
Vince Faller - Predictum
txnelson
Super User

Re: Get Rows that satisfy a condition within a column formula

The formula appears to only being evaluated when the Age column changes.  However, the formula below appears to get you what you want

myheight = :height;
myage = :age;
Sum( :weight[Current Data Table() << get rows where( :age == myage & :height >= myheight )] );
Jim
hogi
Level XI

Re: Get Rows that satisfy a condition within a column formula

Very cool trick to use conditions as Group By options :)

 

Important to note:
With the suggested code, each condition

1) :height <=60

2) :height > 60)

gets it's own result.

There is another variant where the condition is used to restrict the aggregation to one subset - and the result is written to all rows of the respective group. Like in te last column of this table:
hogi_0-1688759525464.png

One option to get there:
Use Tables/Summary with a Data Filter (new in Jmp 17) and merge the values back to the main table:

View more...
Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column("CondSum",Numeric,Continuous,Formula(Col Sum( :weight, :age, :height > 60 )));

dtSum= dt << Summary(
	Local Data Filter(
		Conditional,
		Add Filter( columns( :height ), Where( :height > 60 ) )
	),
	Group( :age ),
	Sum( :weight )
);



dt << Update(
	With( dtSum ),
	Match Columns( :age = :age ),
	Add Columns from Update Table( :"Sum(weight)"n ),
	Replace Columns in Main Table( None )
);


Other approaches (with a variety of speeds) are discussed in  How do I use the Col Maximum Formula with a "where" condition.