49386_en-US

liliconcom

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. 

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 )] );

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

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

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.