Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
user8421
Level I

Column Formula for summary value of group based on values in 2 other columns

Hi everyone -

 

I'm looking for a column formula that would return the following desired column below.  For simplicity in this post each group has 3 rows, but the dataset I currently have has groups with varying numbers of rows.

 

Any help appreciated!

 

 

GroupTimeScoreFirst Time Failed (Desired Column)
A1Pass8
A3Pass8
A8Fail8
B1PassPass
B2PassPass
B6PassPass
C1Pass3
C3Fail3
C7Fail3

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: Column Formula for summary value of group based on values in 2 other columns

@user8421 ,

 

This is just a fun alternative that uses data table formulas. It will work for unequal Group sizes and if the table is not sorted.

The logic is to find the minimum row number for Fails in each Group. Col Minimum(... , By) is the key. Column statistic functions using the By option are very useful, but often overlooked functions.

image.png

 

Formula for  column "Row First Failed"

image.png

Formula for column "Time First Failed". Note if the syntax seems strange, the right-hand formula is Time, then select Subscript from the Row functions and drag column 4, "Row First Failed"

image.png

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Column Formula for summary value of group based on values in 2 other columns

Here is a formula that works with your Example data table.  It has not been tested beyond that, so you may need to make some adjustments

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 9 ),
	New Column( "Group", Character, "Nominal", Set Values( {"A", "A", "A", "B", "B", "B", "C", "C", "C"} ) ),
	New Column( "Time", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 3, 8, 1, 2, 6, 1, 3, 7] ) ),
	New Column( "Score",
		Character,
		"Nominal",
		Set Values( {"Pass", "Fail", "Pass", "Pass", "Pass", "Pass", "Pass", "Fail", "Fail"} )
	)
);

dt << New Column( "First Time Failed",
	character,
	formula(
		If( Row() == 1,
			dt = Current Data Table();
			check = :Score;
			If( :Group == :Group[2],
				Failed = Char( Col Max( :Time, :Group ), Failed = :Score )
			);
		,
			If( :Group == Lag( :Group ),
				If( :Score == "Fail",
					check = "Fail"
				);
				Failed = Char( Col Max( :Time, :Group ) );
			,
				If( check == "Pass",
					lagGroup = Lag( :Group );
					:First Time Failed[dt << get rows where( :Group == lagGroup )] = "Pass";
					Failed = Char( Col Max( :Time, :Group ) );
				);
				check = :Score;
			)
		);
		failed;
	)
);
Jim
Highlighted
gzmorgan0
Super User

Re: Column Formula for summary value of group based on values in 2 other columns

@user8421 ,

 

This is just a fun alternative that uses data table formulas. It will work for unequal Group sizes and if the table is not sorted.

The logic is to find the minimum row number for Fails in each Group. Col Minimum(... , By) is the key. Column statistic functions using the By option are very useful, but often overlooked functions.

image.png

 

Formula for  column "Row First Failed"

image.png

Formula for column "Time First Failed". Note if the syntax seems strange, the right-hand formula is Time, then select Subscript from the Row functions and drag column 4, "Row First Failed"

image.png

View solution in original post

Article Labels