topic Re: Column Formula to Determine Test Order for Repeated Samples in Discussions

Column Formula to Determine Test Order for Repeated Samples

VT23 — Wed, 04 Dec 2024 19:48:43 GMT

Attached is JMP file and want to replicate the Test Order column with column formula. Test Order column is based on SerialNo and TransactionBegDtm across multiple LotNo. Goal is to know if the SerialNo tested 1st vs 2nd based on TransactionBegDtm. I tried formula below, but was not able to get the correct matching sequence.

dt = Current Data Table();

dt << New Column( "Duplicate", Formula( Col Cumulative Sum(If(Row() == Col Min(Row(), :SerialNo, :TransactionBegDtm, :LotNo), 1, 2), :SerialNo, :TransactionBegDtm) );

Re: Column Formula to Determine Test Order for Repeated Samples

jthi — Wed, 04 Dec 2024 20:25:15 GMT

It is easiest of you sort your data first by transactionbegdtm to make sure the ordering is "correct", then you might be able to use something like

Col Cumulative Sum(1, :SerialNo, :LotNo))

Also if there can only be either 1 or 2 as test order, you can use something like

	If(:TransactionBegDtm == Col Min(:TransactionBegDtm, :SerialNo, :LotNo), 
		1
	, 2
	)

Re: Column Formula to Determine Test Order for Repeated Samples

VT23 — Wed, 04 Dec 2024 23:18:26 GMT

Thanks for the quick response, I was able to use the second method in your solution and that worked out.