topic Re: Get Rows that satisfy a condition within a column formula in Discussions

Get Rows that satisfy a condition within a column formula

uday_guntupalli — Mon, 08 Jan 2018 17:10:56 GMT

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

cwillden — Mon, 08 Jan 2018 21:25:36 GMT

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.

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

mann — Wed, 01 Sep 2021 15:01:44 GMT

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

vince_faller — Wed, 01 Sep 2021 15:23:36 GMT

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

txnelson — Wed, 01 Sep 2021 15:34:15 GMT

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

hogi — Fri, 07 Jul 2023 20:34:17 GMT

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:

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:

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