Looking to generate two columns based on some pattern from other columns

HSS

Hi All,

I am looking to generate two new columns based on patterns in other two columns ( "Required column1" and "Required Column2"). Please see the attached image -

 

Any help/suggestion ?

Thanks. 

Sample Data.jmp

jthi

I gave a go with this. I try to avoid using Lag() whenever possible and replace it with Col functions. The most difficult formula seems to be Required Column 1 and I couldn't quickly figure out good formula without using Lag() (there is most likely a solution with Col Cumulative Sum somehow). One idea would be to fill places between Start and End with "Ongoing" or something similar, then you could maybe use Col Cumulative Sum easier, but then the most difficult formula would most likely just move to Start and End.

View more...

Names Default To Here(1);

dt = Current Data Table();
//Numbering of Occurrence
dt << New Column("NumberOfOcc", Numeric, Nominal, Formula(
	If(:Sensor == "Primary",
		Col Cumulative Sum(:Sensor == "Primary", :ID, :"Sub-set"n, :Occurrence),
		.
	);
));

//Start and End
dt << New column("StartEnd", Character, Nominal, Formula(
	If(:NumberOfOcc == 1, 
		"End"
	, Col Max(:NumberOfOcc, :ID,:"Sub-set"n, :Occurrence) == :NumberOfOcc,
		"Start"
	, //else
		""
	)
));

//Required Column 1, this could most likely be made somehow with Col Cumulative Sum
dt << New column("Req1", Numeric, Ordinal, Formula(
	If(Row() == 1,
		val = .
	);
	If(:StartEnd == "Start",
		val = 1
	, IsMissing(:StartEnd) | :StartEnd == "End",
		val += 1;
	);
	If(:Lag(:StartEnd) == "End" | (Row() == Col Max(Row()) & :StartEnd == "Start") | (:StartEnd == "Start" & Lag(:StartEnd, -1) == "End"), //more missing checks can be added here
		val = .,
		val;
	)
));

//Required Column 2
dt << New column("Req2", Numeric, Nominal, Formula(
	If(!IsMissing(:Req1),
		Col Cumulative Sum(:StartEnd == "Start",
			:ID, :"Sub-set"n
		)
	,
		.
	)
));

If you have time, I would suggest testing both ways of solving this, the one @txnelson has provided with more heavy use of Lag and comparisons, and the one I have with Col formulas. Both definitely have their use cases and understanding them will help you in a long run when writing formulas/calculations in JMP. And in some cases when you understand how to combine both, you can solve quite difficult tasks with just formulas.

 

Also after you get the logic working, I would most likely remove Formulas from the datatable (or replace Formula() with << Set Each value in script), because this will most likely break if order is changed in datatable.